Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

i have 2 tables Message and Attachments as follows (note: this is a one to many relationship:

message: messageId, from, to, subject, body

Attachments: messageId, filename

i want to select the basic message data and include a bit field to denote whether the message has attachments or not. i am trying to use exists in my sql posted below. this doesn't work - whats the way to do what im lookign for?

select  
M.*,
hasAttachments = EXISTS(select AttachmentId from Attachment where messageId = M.messageId),
FROM Message M
share|improve this question

2 Answers

up vote 2 down vote accepted

I'd do it with a CASE statement:

select
    m.*,
    hasAttachments = CASE WHEN EXISTS(select * from Attachment where messageId = M.messageId) then 1 else 0 end
from Message M

or

select distinct 
    m.*,
    hasAttachments = CASE WHEN a.AttachmentId is not null then 1 else 0 end
from Message m
left join Attachment a on a.MessageId = m.MessageId
share|improve this answer
The 2nd one would require an aggregate/distinct because it's zero to many – gbn May 17 '11 at 19:07
It's a left join.. what am I missing? I wrote it without any sql server in front, so I'm sure I might make a mistake, but can't see it now :). – Marian May 17 '11 at 19:09
many attachments for one message would give many output rows. When one is expected. Your first construct avoids that. I used CROSS APPLY not LEFT JOIN. Note: our first solutions do similar but your EXISTS is more efficient than my COUNT. – gbn May 17 '11 at 19:14
Ha, you're right, my bad. Thanks! – Marian May 17 '11 at 19:18
@gbn: i read that in sql2005 onward the count and exists are the same because count is treated like exists: blogs.technet.com/b/wardpond/archive/2007/08/27/… – kacalapy May 17 '11 at 19:39
show 1 more comment
up vote 2 down vote 
select  
    M.*,
   CAST(select COUNT(*) from Attachment A where A.messageId = M.messageId) AS bit) AS hasAttachments,
FROM Message M

or

select  
    M.*,
   CAST(hasAttachments AS bit) AS hasAttachments,
FROM
  Message M
  CROSS APPLY
  (select COUNT(*) AS hasAttachments
   from Attachment A
    where A.messageId = M.messageId) A2
share|improve this answer
How are the two options as far as performance? is one better? – kacalapy May 17 '11 at 19:35
probably the same... you'd have to test – gbn May 17 '11 at 19:48
I like this, and you can even avoid a cast to bit if you use select count(distinct 1) in the cross apply. – ajk May 18 '11 at 14:33
@AJ01: it'll still be int. I went for explicit bit type instead of just 0 or 1 – gbn May 18 '11 at 14:35
True enough, fair point. – ajk May 18 '11 at 15:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.