SQL query with 2 foreign keys from one table

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

开发者_如何学C

The result should look like:

Id | OwnerName | ClientName

---

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

---

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....