Right you are Shawn; Harald's answer was dead-on. Of course, I missed it the first time, b/c I was distracted by the typo and his answer was subtle--it took your reply to Martin to make me realize that he'd rearranged the clauses.
Many thanks to all three of you! --Chris -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 13, 2004 10:33 AM To: Martin Gainty Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] 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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
