There's something I'm not getting about how to put a SELECT restriction on a query
with an outer join. The following query:
SELECT Applicants.AppID, Applicants.Name, Applicants.Email,
Reviews.Quant, Reviews.Qual
FROM ApplicantStatus
INNER JOIN Applicants ON Applicants.AppID = ApplicantStatus.AppID
LEFT JOIN Reviews ON Reviews.AppID = Applicants.AppID
WHERE ApplicantStatus.Active = 1
AND ApplicantStatus.SCode = '####'
AND C.Reviewer.ID = 2;
returns only Applicants who have reviews from Reviewer # 2. What I want is *all*
applicants who meet the other two criteria (Active, and SCode =...), and *any* reviews
by Reviewer 2 for any of those applicants (if Reviewer 2 hasn't written for Applicant
a, then a should still be in the result set, but with the Reviews.* columns as NULL).
When I remove the final "ReviewerID = 2" restriction, all of the right applicants are
in the dataset--but with a lot of extra rows due to reviews by other reviewers. How
do I get rid of Reviewers {1, 3...n}, without losing all the applicants who've never
met Reviewer #2?
n.b. I can't use subqueries--I'm stuck with MySQL 4.0.1.
Thanks! --Chris
