I think this should work... SELECT b.Name AS "Boss name", ad.Name AS "Admin name", as.Name AS "Assistant name" FROM Projects AS pr LEFT JOIN People AS b ON b.ID = pr.Boss_ID LEFT JOIN People AS ad ON ad.ID = pr.Admin_ID LEFT JOIN 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? > > Projects > ---------- > Project_ID > Boss_ID > Admin_ID > Assistant_ID > > People > -------- > ID > Name > > Can I do something like this: > > SELECT > * 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 > Assistant_Name > > I know that syntax isn't right but I'm not sure where to go. > -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
