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.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 -- 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]
