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]
