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?

Thanks,

Answers


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

Need Your Help

Nested SQL case statement

sql sql-server case-statement

I'm trying to bucket unit statuses. What am I doing wrong with my case statement? I'm new to SQL.

What are some ways to install and update a .NET AutoCAD plug-in

.net installer windows-installer autocad

Right now I have a set of tools for AutoCAD that get deployed to users in our company. Currently they are being deployed via an msi that I maintain through a Visual Studio Setup Project. The files

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.