Martin, you are correct in how you determine when to use AND and when to
use OR, but that's not what the original query was trying to find.... If
you re-read his original post, he wants this query:
SELECT Applicants.AppID,
Applicants.Name,
Applicants.Email
FROM ApplicantStatus
INNER JOIN Applicants
ON Applicants.AppID = ApplicantStatus.AppID
WHERE ApplicantStatus.Active = 1
AND ApplicantStatus.SCode = '####';
with two additional columns containing information from the "reviews"
table. BUT! he only wants those columns populated if the reviewer was
reviewer #2. That's why Harald's answer is correct.
To repeat Harald's answer:
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 ReviewerID = 2
WHERE ApplicantStatus.Active = 1
AND ApplicantStatus.SCode = '####';
With the ReviewerID condition into the ON clause of the LEFT JOIN (and not
as a condition in the WHERE clause), the query will not join ANY row from
"reviews" for ANY OTHER reviewer except #2. Good call Harald!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Martin Gainty" <[EMAIL PROTECTED]> wrote on 10/13/2004 10:13:46 AM:
> 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]
>
