I have two tables and there's a one to many relationship between the two. My query has a
(SELECT SUM(CASE WHEN a.ContentId IS NULL THEN 1 ELSE 0 END)) > 0
END as 'HasAttachments'
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.