MSSQL query with join and where clause

Question

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

Answer 1

My only real comment to make is that the subquery is irrelevant. You can achieve the same result without it.

---

When specifying your column aliases in the outer query. There is no need for you to use AS picid as the column is already called picid.

---

You could also use table aliases as well to shorten some of the statements. I personally prefer to see aliases than fully qualified table names. Assuming everyone sticks to a naming convention of course. My normal rule of thumb is that each capital letter of the table becomes the alias.

---

Also, as we can remove the subquery, we can change the WHERE clause so it is no longer filtering out the attachment name but instead the PK (WHERE a.Id IS NOT NULL).

---

For the columns being returned, can we make them more explicit? i.e. CandidateName instead of CName, etc.

Here is the modified version of your query using the changes above:

SELECT
     c.Name     AS CandidateName
    ,c.picid    AS PicId
    ,a.Name     AS AttachmentName
FROM Candidates c
LEFT JOIN AttachmentCandidate ac ON c.Id = ac.CandidateId
LEFT JOIN Attachments a ON ac.AttachmentId = a.Id
WHERE c.picid IS NOT NULL
OR a.Id IS NOT NULL

---

I'm not sure whether the formatting is just bad when you created your question or whether your SQL query is like that? If so, it's badly formatted and difficult to read. I try and break up each part of the query into separate segments so it's easy to read and maintain by others:

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)

As you requested an alternative, i'll give you one. Essentially it's pretty much the reverse of what you're doing. I'm gathering all of the candidates with attachments first and then I RIGHT JOIN back onto the candidates table and filter out the ones without a PicId.

SELECT
    c.Name      AS CandidateName
    ,c.PicId    AS PicId
    ,a.Name     AS AttachmentName
FROM AttachmentCandidate ac
INNER JOIN Attachments a ON a.Id = ac.AttachmentId
RIGHT JOIN Candidates c ON c.Id = ac.CandidateId
WHERE c.picid IS NOT NULL
OR a.Id IS NOT NULL