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]
