Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/forums/mysql-forum/)
-   -   inner join problem (http://www.go4expert.com/forums/inner-join-problem-t4023/)

cbesh2 25Apr2007 22:22

inner join problem
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:


$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());

pradeep 26Apr2007 10:04

Re: inner join problem
You need to use table aliases

Code: SQL

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

cbesh2 26Apr2007 20:12

Re: inner join problem
Thank you so much! I never realized you could do that. You've been a tremendous help!

All times are GMT +5.5. The time now is 21:06.