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]
