1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

conditional join

Discussion in 'MySQL' started by cbesh2, Apr 30, 2007.

  1. cbesh2

    cbesh2 New Member

    Joined:
    Mar 6, 2007
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    0
    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.
     
  2. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    Use LEFT JOIN on the table where data may or may not be present.
     
  3. cbesh2

    cbesh2 New Member

    Joined:
    Mar 6, 2007
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    0
    Pefect. Still trying to figure out how these tables join together. Thanks for the solution.
     
  4. cbesh2

    cbesh2 New Member

    Joined:
    Mar 6, 2007
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    0
    Guess that's why I couldn't find any results when searching for "conditional join". :)
     
  5. pradeep

    pradeep Team Leader

    Joined:
    Apr 4, 2005
    Messages:
    1,646
    Likes Received:
    86
    Trophy Points:
    0
    Occupation:
    Programmer
    Location:
    Kolkata, India
    Home Page:
    Code:
     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  
     
     
  6. MySQLuser

    MySQLuser New Member

    Joined:
    Feb 10, 2010
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    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
     

Share This Page