List:General Discussion« Previous MessageNext Message »
From:Ian Simpson Date:September 30 2009 10:31am
Subject:Re: Simply join that confuses me.
View as plain text  
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

Thread
Simply join that confuses me.Brian Dunning30 Sep
  • Re: Simply join that confuses me.Ian Simpson30 Sep