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.FacultyMember > > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
