sql select count
I have two tables: ca开发者_JS百科lls 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 descriptionattachments
attach_id attach_name call_idIf 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,
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
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
精彩评论