Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/forums/mysql-forum/)
-   -   conditional join (http://www.go4expert.com/forums/conditional-join-t4089/)

cbesh2 1May2007 01:19

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.

pradeep 1May2007 12:31

Re: conditional join
 
Use LEFT JOIN on the table where data may or may not be present.

cbesh2 1May2007 18:56

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

cbesh2 1May2007 18:57

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

pradeep 2May2007 10:04

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


MySQLuser 10Feb2010 12:51

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


All times are GMT +5.5. The time now is 00:07.