Simply join that confuses me.

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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Simply join that confuses me.

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=gcdmg-mysql-2@m.gmane.org