hotdiggitydoddo hotdiggitydoddo - 3 months ago 13
SQL Question

Aggregate sub-query not producing expected results

I have two tables and there's a one to many relationship between the two. My query has a

group by
and I want to determine if there are any results in the 2nd table that match some criteria. I couldn't figure it out with a sub-query and tried the following code, but it's not giving you the results you expect.

CASE WHEN
(SELECT SUM(CASE WHEN a.ContentId IS NULL THEN 1 ELSE 0 END)) > 0
THEN 1
ELSE 0
END as 'HasAttachments'


Basically, I am trying to figure out if my message (which could have many attachments) has any attachments where the
ContentId
is
null
and if that
count
is greater than
0
then I want to return the
boolean
value in
HasAttachments
.

Any help would be great!

Answer
CASE WHEN 
        SUM(CASE WHEN a.ContentId IS NULL
                       AND a.message_id IS NOT NULL
                 THEN 1
                 ELSE 0
            END) > 0 
THEN 1 
ELSE 0 
END as HasAttachments

From your narratives it seems like your inner Case Statement is missing another condition to determine when there is actually an attachment. If you don't put in the second condition of when the AttachmentTable.message_id IS NOT NULL then you will count both messages that don't have any attachments and those messages that have attachments but no content id as the same thing. But adding the a.message_id you limit that to just the case you seem to desire from your narrative.

Comments