WOW! It worked! What's the darn difference? Man, you're the greatest! I'm really really glad that we have people like you who are so willing and kind enough to help others!
Your help is greatly appreciated!!!!!!!!!!!!! :) Jordan David Sears wrote: > OK, > > The server is complaining about the parentheses. Here's a version of the > query which is not nested. It may actually be close to what you want...:) > > select distinct IE.title, FP.LastName, FP.FirstName > from InstitutionEmployment IE > left join FacultyPerson FP on IE.FacultyMember=FP.FacultyPersonID > left join TenureDescription TD on IE.Tenure=TD.TenureDescriptionID > left join TenureType TT on TD.Tenure_Status=TT.TenureTypeID > where IE.Department = '$dept' > and FP.LastName like '%".$lname."%' > and FP.FirstName like '%".$fname."%' > and IE.Rank = '$rank' > and TD.Tenure_Status like '$tenure' > order by FP.FirstName; > > -----Original Message----- > From: Jordan Morgan [mailto:[EMAIL PROTECTED] > Sent: Sunday, September 14, 2003 4:28 PM > To: David Sears > Subject: Re: Newbies asking help with LEFT JOIN > > Hmm.... did what you suggested using LEFT JOIn and remove the parentheses > but I still got the following message: > > Error in query: select distinct FP.FacultyPersonID, IE.Title, IE.Department, > FP.LastName, FP.FirstName, IE.School from InstitutionEmployment IE left join > FacultyPerson FP on IE.FacultyMember=FP.FacultyPersonID left join > TenureDescription TD left join TenureType TT on > TD.Tenure_Status=TT.TenureTypeID on IE.Tenure=TD.TenureDescriptionID where > IE.Department = '1' and TD.Tenure_Status='1' order by FP.FirstName. You have > an error in your SQL syntax near 'left join TenureType TT on > TD.Tenure_Status=TT.TenureTypeID on IE.Tenure=TD.Tenu' at line 1 > > I'm so lost.... > > David Sears wrote: > > > One more thought: I removed the parentheses altogether (with no other > > changes) and the query still parses correctly on 4.0.14. You might > > try the same on your server. > > > > -----Original Message----- > > From: Jordan Morgan [mailto:[EMAIL PROTECTED] > > Sent: Sunday, September 14, 2003 3:00 PM > > To: David Sears > > Cc: [EMAIL PROTECTED] > > Subject: Re: Newbies asking help with LEFT JOIN > > > > shoot, my MySQL is of version 3.23.54. :(( how do I modify mine to > > make it work then? Thanks! > > > > ** it's hard to be a newbie** > > > > David Sears wrote: > > > > > I see the syntax error output on your search page. I've tested the > > > query against empty tables on mysql version 4.0.14 without error. I > > > copied the query from the output of your search page to my client, > > > and it still doesn't error on 4.0.14. > > > > > > I guess I should have asked which version of the server you're > > > using. I'm not sure when all the various joins took effect. There > > > may be some such info in the mysql docs. > > > > > > -----Original Message----- > > > From: Jordan Morgan [mailto:[EMAIL PROTECTED] > > > Sent: Sunday, September 14, 2003 2:45 PM > > > To: David Sears > > > Cc: [EMAIL PROTECTED] > > > Subject: Re: Newbies asking help with LEFT JOIN > > > > > > I tried the parentheses () first and still it didn't work. :( > > > > > > David Sears wrote: > > > > > > > That's parentheses () around the inner join, not braces {}. > > > > > > > > -----Original Message----- > > > > From: Jordan Morgan [mailto:[EMAIL PROTECTED] > > > > Sent: Sunday, September 14, 2003 12:56 PM > > > > To: David Sears > > > > Cc: [EMAIL PROTECTED] > > > > Subject: Re: Newbies asking help with LEFT JOIN > > > > > > > > Thanks for the prompt response David. However, it still doesn't > > > > work. > > > > > > > > The search page is here: > > > > http://www.jordymaeproductions.com/PHP/latest/SearchFaculty.php4 > > > > > > > > I have a faculty from IT department named Debbie Farmer who is an > > > > instructor with a tenure status of "Not on Tenure Track". So if I > > > > typed in D in FirstName, L in LastName, chose Instructor in Rank > > > > and Not on Tenure Track in Tenure Status field, her record was > > > > pulled. But if I changed the the tenure status to the wrong tenure > > > > status, her record still shows up. The query was supposed to > > > > trigger when if > > > > (!empty($lname) && !empty($fname) && > > > > !empty($rank) && !empty($tenure)). So I don't know what's missing to > > > > cause her record pulled out even without the incorrect tenure > > > > status. > > > > > > > > I don't like to type too much either so I gotta learn to use the > > > > table reference thingy. ;) > > > > > > > > Any ideas to solve my query error? > > > > > > > > Thanks millions! > > > > > > > > Jordan > > > > > > > > David Sears wrote: > > > > > > > > > I didn't have the fortitude to populate the tables with data, so > > > > > I don't know if this will work for you, but to use explicit > > > > > joins try something like the sample below. (Note that I don't > > > > > like to type as much as you do:-) > > > > > > > > > > select distinct IE.title, FP.LastName, FP.FirstName from > > > > > InstitutionEmployment IE left join FacultyPerson FP on > > > > > IE.FacultyMember=FP.FacultyPersonID > > > > > left join (TenureDescription TD > > > > > inner join TenureType TT on TD.Tenure_Status=TT.TenureTypeID) on > > > > > IE.Tenure=TD.TenureDescriptionID where IE.Department = '$dept' > > > > > and FP.LastName like '%".$lname."%' and FP.FirstName like > > > > > '%".$fname."%' and IE.Rank = '$rank' and TD.Tenure_Status like > > > > > '$tenure' order by FP.FirstName; > > > > > > > > > > -----Original Message----- > > > > > From: Jordan Morgan [mailto:[EMAIL PROTECTED] > > > > > Sent: Sunday, September 14, 2003 1:29 AM > > > > > To: [EMAIL PROTECTED] > > > > > Subject: Newbies asking help with LEFT JOIN > > > > > > > > > > Hi, > > > > > > > > > > I'm working on a faculty system and currently I'm building a > > > > > search which allows users to generate a list of faculty withint > > > > > a department based on their first name, last name, rank, and > > > > > tenure status. I could do it without the tenure status criteria > > > > > however I can't get it work if the tenure status is included in > > > > > the query. > > > > > > > > > > Rank field on the search page is generated by using the Rank > > > > > table while the Tenure Status field on the search page is > > > > > generated by using the TenureType table. > > > > > > > > > > Structure of 4 tables used in the query: > > > > > > > > > > FacultyPerson: > > > > > > > > > > FacultyPersonID tinyint(4) unsigned PRI auto_increment > > > > > FirstName varchar(25) > > > > > LastName varchar(25) > > > > > > > > > > InstitutionEmployment: > > > > > > > > > > EmploymentID int(11) No auto_increment > > > > > FacultyMember int(11) > > > > > Department int(11) > > > > > Rank int(11) > > > > > Title varchar(50) > > > > > Tenure int(11) (FacultyMember=FacultyPerson.FacultyPersonID and > > > > > Tenure=TenureDescription.TenureDescriptionID) > > > > > > > > > > TenureDescription: > > > > > > > > > > TenureDescriptionID int(11) > > > > > FacultyMember int(11) > > > > > Tenure_Status int(11) > > > > > Award_Date date > > > > > (Tenure_Status=TenureType.TenureTypeID) > > > > > > > > > > TenureType: > > > > > > > > > > TenureTypeID int(11) > > > > > Tenure_Type varchar(25) > > > > > > > > > > Provided that dept exists and all 4(first name, last name, rank, > > > > > and tenure > > > > > status) criteria have been entered, my query is: > > > > > > > > > > SELECT DISTINCT InstitutionEmployment.Title, > > > > > FacultyPerson.LastName, FacultyPerson.FirstName from > > > > > InstitutionEmployment, FacultyPerson, TenureDescription, > > > > > TenureType where InstitutionEmployment.Department='$dept' > > > > > AND > > > > > FacultyPerson.FacultyPersonID=InstitutionEmployment.FacultyMembe > > > > > r > > > > > AND > > > > > InstitutionEmployment.Tenure=TenureDescription.TenureDescriptionID > > > > > AND TenureDescription.Tenure_Status=TenureType.TenureTypeID AND > > > FacultyPerson.LastName LIKE '%".$lname."%' AND > > > FacultyPerson.FirstName > > > > LIKE > > > > > '%".$fname."%' AND InstitutionEmployment.Rank='$rank' AND > > > > > TenureDescription.Tenure_Status='$tenure' ORDER BY > > > > FacultyPerson.FirstName; > > > > > > > > > > It seemed that my query returns results without verifying the > > > > > tenure status criteria at all. I think my logic is right but it > > > > > doesn't work. With the complexity of the query, I don't know how > > > > > to 1) make it work and 2) make it work using LEFT JOIN. > > > > > > > > > > Any help is highly appreciated. > > > > > > > > > > Thanks! > > > > > > > > > > Regards, > > > > > > > > > > Jordan > > > > > > > > > > -- > > > > > 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] > > > > > > -- > > > Jordan Morgan > > > Information Analyst - GeorgiaFIRST HRMS Project > > > Board of Regents > > > Office of Information and Instructional Technology > > > 1865 West Broad Street, Athens, GA 30606-3539 > > > Phone: (706) 369-6232 > > > Fax: (706) 369-6429 > > > mailto:[EMAIL PROTECTED] http://www.usg.edu > > > > -- > > Jordan Morgan > > Information Analyst - GeorgiaFIRST HRMS Project > > Board of Regents > > Office of Information and Instructional Technology > > 1865 West Broad Street, Athens, GA 30606-3539 > > Phone: (706) 369-6232 > > Fax: (706) 369-6429 > > mailto:[EMAIL PROTECTED] http://www.usg.edu > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- > Jordan Morgan > Information Analyst - GeorgiaFIRST HRMS Project > Board of Regents > Office of Information and Instructional Technology > 1865 West Broad Street, Athens, GA 30606-3539 > Phone: (706) 369-6232 > Fax: (706) 369-6429 > mailto:[EMAIL PROTECTED] http://www.usg.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
