sql select count

Question

I have two tables: calls and attachments and I want to display everything that's in the calls table but also display whether a call has attachments, - by determining if there is an attachment record with a call_id in it. Maybe there is attachments, maybe there isn't.

calls
call_id
title
description

attachments
attach_id
attach_name
call_id

If I write:

select call_id, title, description from calls

to give me a list of all calls....

How can I also include whether this call record has an attachment(s) or not?

Thanks,

Answer 1

select a.call_id, a.title, a.description, count(b.attach_id)
from calls a, attachments b
where a.call_id = b.call_id
group by a.call_id, a.title, a.description
union all
select distinct a.call_id, a.title, a.description, 0
from calls a, attachments b
where not exists (select call_id from calls c where c.call_id = a.call_id)

this will give a count of attachments for every call with the same title and description

Answer 2

You can use an outer join to accomplish this:

SELECT c.call_id, title, description, attach_name
FROM calls c
LEFT OUTER JOIN attachments a ON c.call_id = a.call_id

The above will display (NULL) for the attach_name if no attachment is found. You can use ISNULL() to supply a default value if no attach_name is found, such as:

SELECT c.call_id, title, description, 
    ISNULL(attach_name, '(No attachment)') as attach_name
FROM calls c
LEFT OUTER JOIN attachments a ON c.call_id = a.call_id