Reputation: 10332
I have a table with two foreign keys in one table.
Table PROJECTS - Id - Owner - FK - Client - FK
and
table USERS - Id - Name
I'd like to select all projects with appropriate names of the owner and client
The result should look like:
Id | OwnerName | ClientName
Upvotes: 2
Views: 723
Reputation: 2767
You can join to a table multiple times and make friendly output names using brackets:
SELECT P.ID AS [Projects ID],
U1.Name AS [Owner Name],
U2.Name AS [Client Name]
FROM Projects P
LEFT OUTER JOIN Users U1 ON (P.OwnerID = U1.ID)
LEFT OUTER JOIN Users U2 ON (P.ClientID = U2.ID)
Also anytime we have an id where I work we usually include that in the FK name. So my answer pretends that is the case...
Also if the P.OwnerID and P.ClientID are required entries in Projects you can use an INNER JOIN instead of a LEFT OUTER JOIN....
Upvotes: 2
Reputation: 72930
You just need two joins to that table.
SELECT p.Id,
u1.Name OwnerName,
u2.Name ClientName
FROM Projects p
LEFT JOIN
Users u1
ON p.Owner = u1.Id
LEFT JOIN
Users u2
ON p.Client = u2.Id
Upvotes: 11