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