In article <[EMAIL PROTECTED]>,
"Christopher J. Mackie" <[EMAIL PROTECTED]> writes:
> 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?
If "C.Reviewer.ID" is a typo for "Reviews.ReviewerID", the solution is
simple:
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
AND C.Reviewer.ID = 2
WHERE ApplicantStatus.Active = 1
AND ApplicantStatus.SCode = '####';
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
