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

inner join problem

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

  1. cbesh2

    cbesh2 New Member

    Joined:
    Mar 6, 2007
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    0
    I have two tables:
    * JobInfo - gives information about each job
    * Employees - lists employees and their information

    In each job, I need to create an entry that assigns a draftsman and a salesperson to each project (in the JobInfo table). I can't figure out how to join both the salesperson and the draftman fields to the employees table. Can somebody tell me what I'm doing wrong? Thanks in advance for the help.

    Below is my code:

    Code:
    $result= mysql_query ("
    SELECT JobInfo.SystemNumber, JobInfo.dwgnumber, JobInfo.quotenumber, JobInfo.ponumber, JobInfo.jobnumber, JobInfo.salesperson, JobInfo.draftsman, Employees.empID, Employees.empfirstname, 
    Employees.emplastname, JobInfo.store, Store.storeID, Store.storecity, Store.storestate, customers.ID, customers.firstname, customers.lastname
    FROM JobInfo 
    INNER JOIN Employees ON JobInfo.salesperson=Employees.empID AND JobInfo.draftsman=Employees.empID
    INNER JOIN Store ON JobInfo.store=Store.storeID
    INNER JOIN customers ON Store.owner=customers.ID
    ORDER BY dwgnumber DESC Limit 10 
    ") or die (mysql_error());
     
  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:
    You need to use table aliases

    Code:
    SELECT j.SystemNumber, j.dwgnumber, j.quotenumber, j.ponumber, j.jobnumber, j.salesperson, j.draftsman, e1.empID, e1.empfirstname, 
    e1.emplastname, e2.empID, e2.empfirstname, 
    e2.emplastname, j.s, s.sID, s.scity, s.sstate, c.ID, c.firstname, c.lastname
    FROM JobInfo j 
    INNER JOIN Employees e1 ON j.salesperson=e1.empID
    INNER JOIN Employees e2 ON j.draftsman=e2.empID
    INNER JOIN Store s ON j.s=s.sID
    INNER JOIN customers c ON s.owner=c.ID
    ORDER BY dwgnumber DESC Limit 10 
    
     
  3. cbesh2

    cbesh2 New Member

    Joined:
    Mar 6, 2007
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    0
    Thank you so much! I never realized you could do that. You've been a tremendous help!
     

Share This Page