CODEWITHSUNDEEP
sqlsql-serverjoinselect
aaarianme
aaarianme

Reputation: 292

Join on two of the same foreign keys

I have this table below

Table Assignments
AssignmentID:  int
LinkedTo:  varchar(50)
AssignedUser:  int
AssignedBy:  int

where AssignedBy and AssignedUser are foreign keys from the table Users. Here is what Users looks like

UserKey:int
Username:varchar(50)

How can I do an inner join where I get both AssignedBy and AssignedUser? The following gives me one of them but how can I get both? By the way AssignedBy and AssignedUser are two different Users. I'm trying to get Users.Username

select Users.Username
from Assignments
INNER JOIN Users ON Assignments.UserKey = Users.UserKey

Upvotes: 1

Views: 61

Answers (2)

Bill Tür stands with Ukraine
Bill Tür stands with Ukraine

Reputation: 3515

If you want both user names on one row, you have to join Users twice, like

SELECT UA.Username AS AssignedUserName, UB.Username AS AssignedByUserName
    FROM Assignments A
    INNER JOIN Users UA ON A.AssignedUser = UA.UserKey
    INNER JOIN Users UB ON A.AssignedBy = UB.UserKey;

If your goal is to have all user names regardless wether it comes from AssignedUser or from AssignedBy, you have to use UNION

SELECT U.Username
    FROM Assignments A
    INNER JOIN Users U ON A.AssignedUser = U.UserKey
UNION SELECT U.Username
    FROM Assignments A
    INNER JOIN Users U ON A.AssignedBy = U.UserKey;

Note however that this will remove duplicate user names. If you want to keep duplicates, use UNION ALL.

Upvotes: 3

Mureinik
Mureinik

Reputation: 312219

You need two joins:

SELECT assigned.username AS assigned_username
       assigned_by.username AS assigned_by_username
FROM   assignments a
JOIN   users assigned ON a.assigneduser = assigned.userkey
JOIN   users assigned_by ON a.assignedby = assigned_by.userkey

Upvotes: 1

Related Questions