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
1 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