Just JOIN them all, as:

SELECT S.Nam, E.Name, M.Name FROM Jobs
	INNER JOIN Employees S ON Jobs.Salesman = S.ID
	INNER JOIN Employees E ON Jobs.Engineer = E.ID
	INNER JOIN Employees M ON Jobs.Manager  = M.ID
	WHERE JobID = 123;

The trick is give each reference to the three instances of the Employees
table a separate alias, so you can refer to the correct Employees.Name

--
Jeff Mason			Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: Mike Deck [mailto:MichaelBDeck@t...]
Sent: Friday, July 26, 2002 3:57 PM
To: sql language
Subject: [sql_language] Multiple Joins Between 2 Tables


Hello,

I am trying to build a query to select the names of employees out of a
lookup table that correspond to a given job.  The only problem is each job
has multiple roles that all associate back to the same employees table.

Jobs:

JobID
Salesman     Foreign Key to Employees.ID
Engineer     Foreign Key to Employees.ID
Manager      Foreign Key to Employees.ID


Employees:

ID
Name
Department
etc.


If I wanted to get the name of the salesman for a job 123 I could use:

SELECT Employees.Name FROM Jobs INNER JOIN Employees ON Jobs.Salesman 
Employees.ID WHERE JobID = 123

What if I want to select the name of the Salesman, Engineer, and Manager.
I have no idea how to do the joins for that.  Any help would be much
appreciated.