JWalker1995 JWalker1995 - 3 months ago 7
SQL Question

SQL Inner Join Two Foreign Keys

I have two tables (

Users
and
Pairs
). The
Pairs
table contains 3 columns, an ID and then a user1ID and user2ID.

Users

ID firstName surname
------------------------------
1043 john doe
2056 jane doe


Pairs

ID user1ID user2ID
------------------------------
1 1043 2056


I'm then looking at using a select statement to get the user details base on the ID of the
Pairs
table:

SELECT users1.*, users2.*
FROM Pairs
JOIN Users users1 ON Pairs.user1ID = users1.IDNumber
JOIN Users users2 ON Pairs.user2ID = users2.IDNumber
WHERE Pairs.ID = 1


Which returns the right details for the two users, however they're all on one row, how can I get it to return each user on a separate row as they are in the
Users
table?

Answer
SELECT users1.*, users2.*
FROM Pairs 
JOIN Users
ON Pairs.user1ID = users.IDNumber
OR Pairs.user2ID = users.IDNumber
WHERE Pairs.ID = 1

Just use an OR statement in your ON condition instead of 2 joins.