I've the following tables
Candidates Id Name PicId 1 Tom NULL 2 James 1 3 Bob NULL 4 Manny 2 Attachments Id Name 1 One.jpg 2 Two.doc 3 Three.rtf The join table AttachmentCandidate CandidateId AttachmentId 3 1 3 2 4 3
So from the above tables it shows that a candidate's 'PicId' is nullable & one candidate can have 0 or many attachments.
Now the query what I wish to create is the one which should return me Candidate's Name, PicId, & Attachment Name, but I don't want the candidates who neither have a PicId nor any Attachments.
I was able to achieve it with a subquery. Is there any better way it can be done??
SELECT r.cname,r.picId,r.attName FROM (SELECT Candidates.Name as cname, Candidates.PicId as picId, Attachments.Name as attName FROM Candidates LEFT JOIN AttachmentCandidate ON Candidates.Id = AttachmentCandidate.CandidateId LEFT JOIN Attachments ON AttachmentCandidate.AttachmentId = Attachments.Id) as r WHERE (r.picId IS NOT NULL OR r.attName IS NOT NULL) Result cname picId attName James 1 NULL Bob NULL One.jpg Bob NULL Two.doc Manny 3 Three.rtf
And the candidate 'Tom' not is present in the result since he doesn't have a picId nor any attachments