Go4Expert - conditional join

Go4Expert (http://www.go4expert.com/)

- MySQL (http://www.go4expert.com/forumdisplay.php?f=42)

- - conditional join (http://www.go4expert.com/showthread.php?t=4089)

conditional join

I am having the hardest time trying to figure out how to join tables if data is not present. The field "draftman" is sometimes known and sometimes NULL. I don't have a problem when a value is present, but how can I join the tables JobInfo and Employees when the draftsman value is NULL?

The line

Code:

inner join Employees as dm on JobInfo.draftsman = dm.empID

is where the JobInfo and Employees tables are joined when draftsman is present.

Below is my code:

Code:

$result= mysql_query ("

SELECT JobInfo.SystemNumber, 

       JobInfo.dwgnumber, 

       JobInfo.quotenumber, 

       JobInfo.ponumber, 

       JobInfo.jobnumber, 

       JobInfo.salesperson, 

       JobInfo.draftsman, 

       sp.empID as salesId, 

       sp.empfirstname as salesfirstname, 

       sp.emplastname as saleslastname,

       dm.empID as draftId, 

       dm.empfirstname as draftfirstname, 

       dm.emplastname as draftlastname,

FROM JobInfo 

INNER JOIN Employees as sp ON JobInfo.salesperson = sp.empID 

inner join Employees as dm on JobInfo.draftsman = dm.empID  

") or die (mysql_error());

Thanks for your help in advance.

Re: conditional join

Use LEFT JOIN on the table where data may or may not be present.

Re: conditional join

Pefect. Still trying to figure out how these tables join together. Thanks for the solution.

Re: conditional join

Guess that's why I couldn't find any results when searching for "conditional join". :)

Re: conditional join

Code: SQL

SELECT JobInfo.SystemNumber, 
        JobInfo.dwgnumber, 
        JobInfo.quotenumber, 
        JobInfo.ponumber, 
        JobInfo.jobnumber, 
        JobInfo.salesperson, 
        JobInfo.draftsman, 
        sp.empID AS salesId, 
        sp.empfirstname AS salesfirstname, 
        sp.emplastname AS saleslastname,
        dm.empID AS draftId, 
        dm.empfirstname AS draftfirstname, 
        dm.emplastname AS draftlastname,
 FROM JobInfo 
 INNER JOIN Employees AS sp ON JobInfo.salesperson = sp.empID 
 LEFT JOIN Employees AS dm ON JobInfo.draftsman = dm.empID

Re: conditional join

Alternatively, You can use Conditional Left Joins.
It is simple.
You can see comprehensive post on MySQL conditional Joins at
mysqldiary.com/conditional-joins-in-mysql