sql select count
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?
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