Sadly, it was a typo in the email (apologies for that), but not in the
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 Reviews.ReviewerID = 2
AND ApplicantStatus.SCode = '####';
produces the result I described. --Chris
> If "C.Reviewer.ID" is a typo for "Reviews.ReviewerID", the solution is
simple:
>> 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 Reviews.ReviewerID = 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?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
