"You should generally not have any conditions in the ON part that are used to restrict
which rows you want in the result set, but rather specify these conditions in the
WHERE clause"
Forgive me for following the documentation!!
Martin
----- Original Message -----
From: [EMAIL PROTECTED]
To: Martin Gainty
Cc: [EMAIL PROTECTED] ; [EMAIL PROTECTED]
Sent: Wednesday, October 13, 2004 10:33 AM
Subject: Re: Q: outer join w/restriction
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]
>
