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]
