Thursday, May 05, 2005

How to join one row in a table with many related rows in the other table

Suppose I have a table as below:
Table Project
{
ProjectID int
Manager int (secondary key)
Architect int (secondary key)
Client int (secondary key)
}

Table Contact
{
ContactID int (primary key)
Name string
}

Now I want to show the Project table with names of Manager and Architect and the client.
A "join" or a "union" will not work...The solution is very simple:

select Project.ProjectID, a.FirstName as Manager, b.firstname as Architect, c.FirstName as Client from Project,
Contact a,
Contact b,
Contact c
where a.contact_id = project.manager and
b.contact_id = project.architect and
c.contact_id = project.client