I think this should work...
b.Name AS "Boss name",
ad.Name AS "Admin name",
as.Name AS "Assistant name"
FROM Projects AS pr
People AS b
ON b.ID = pr.Boss_ID
People AS ad
ON ad.ID = pr.Admin_ID
People AS as
ON as.ID = pr.Assistant_ID
WHERE pr.Project_ID = 5
I went with LEFT JOIN rather than INNER in case there might be roles
that weren't always filled on a particular project.
On Tue, 2009-09-29 at 22:11 -0700, Brian Dunning wrote:
> I have a table of projects with several columns for the IDs of some
> people in various roles, and then a table of the people. How can I get
> all the people for a given project, but keep their roles straight?
> Can I do something like this:
> * from Projects where Project_ID = 5,
> Name from People where Projects.Boss_ID = People.ID as Boss_Name,
> Name from People where Projects.Admin_ID = People.ID as Admin_Name,
> Name from People where Projects.Assistant_ID = People.ID as
> I know that syntax isn't right but I'm not sure where to go.