SQL COUNT() / LEFT JOIN?

Question

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.

Answer 1

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

Answer 2

SELECT c.call_id, title, description, a.call_id, n.call_id
FROM calls c 
LEFT JOIN attachments a ON c.call_id = a.call_id 
LEFT JOIN notes n ON c.call_id = n.call_id
GROUP BY c.call_id,title,description, a.call_id, n.call_id

If call id is present in fiels 4 or 5, you know you have an attachement or a note

If you need to number of attachement or note, look at other answers, look at AtaTheDev's post.

Answer 3

Use distinct in counts

You have to use distinct in counts because your groups have grown by two different entities. So you have to only count distinct values of each. This next query will return both counts as well as bit values whether there are any attachments and notes.

select
    c.call_id, c.title, c.description,
    count(distinct a.attach_id) as attachments_count,
    count(distinct n.note_id) as notes_count,
    /* add these two if you need to */
    case when count(distinct a.attach_id) > 0 then 1 else 0 end as has_attachments,
    case when count(distinct n.note_id) > 0 then 1 else 0 end as has_notes
from calls c
    left join attachments a
    on (a.call_id = c.call_id)
    left join notes n
    on (n.call_id = c.call_id)
group by c.call_id, c.title, c.description