CODEWITHSUNDEEP
sql
user137348
user137348

Reputation: 10332

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

The result should look like:

Id | OwnerName | ClientName

Upvotes: 2

Views: 723

Answers (2)

DiningPhilanderer
DiningPhilanderer

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

David M
David M

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

Related Questions