SQL COUNT() / LEFT JOIN?

I have three tables: calls, attachments and notes and I want to display everything that's in the calls table, but also display whether a call has attachments and whether the call has notes. - by determining if there is an attachment or note record with a call_id in it. There could be notes and attachments, or there may not be but I would need to know.

Tables structure:

calls:

call_id  |  title  |  description  

attachments:

attach_id  |  attach_name  |  call_id  

notes:

note_id  |  note_text  |  call_id  

If I write:

SELECT c.call_id
     , title
     , description
     , count(attach_id) 
FROM calls c 
LEFT JOIN attachments a ON c.call_id = a.call_id 
GROUP BY c.call_id
       , title
       , description

to give me a list of all calls and the number of attachments.

How can I also add in a column with the number of notes or a column which indicates that there is notes?

Any ideas?

Thanks.

Answers


For the count
SELECT 
     c.call_id, 
     title, 
     description, 
     count(DISTINCT attach_id) AS attachment_count , 
     count(DISTINCT note_id)  AS notes_count 
FROM calls c 
LEFT JOIN attachments a ON c.call_id = a.call_id 
LEFT JOIN notes n ON n.call_id = c.call_id 
GROUP BY c.call_id,title,description
Or for existence (will be more efficient if this is all you need)
SELECT 
     c.call_id, 
     title, 
     description, 
     count(attach_id) AS attachment_count , 
     case
        when exists (select * from notes n WHERE n.call_id = c.call_id) then
            cast(1 as bit)
        else
            cast(0 as bit)
    end as notes_exist
FROM calls c 
LEFT JOIN attachments a ON c.call_id = a.call_id 
GROUP BY c.call_id,title,description

Need Your Help

MySQL: UPDATE trigger. Obtain the value of a column used in UPDATE's where clause if it fail to match any row?

mysql sql triggers sql-update where

MySQL: In update trigger's body, can I obtain the value of a column that is specified in the where clause of the triggering query if the where clause does not match any rows at all?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.