A SQL AND is a restrictive filter
In other words
Your resultset will yield results based on how you structure your query
If you structure your resultset which includes only applicants who have been
seen by "Reviewer2" then state
SELECT ... FROM
WHERE (CONDITION1 AND Applicant.Reviewer = 'Reviewer2')
If you want applicants which includes ALL reviewers INCLUDING those who have
been seen by Reviewer2
SELECT ... FROM
WHERE (CONDITION1 OR Applicant Reviewer='Reviewer2')
HTH,
Martin
To some extent.. sanity is a form of conformity..
~John Nash PhD~
----- Original Message -----
From: "Harald Fuchs" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, October 13, 2004 8:37 AM
Subject: Re: Q: outer join w/restriction
> 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]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
