Aqa Aqa - 9 months ago 28
MySQL Question

Mysql Join with 2 foreign keys in same table referencing same key

I have the 2 tables below.

Table: Users

user_id username
-- --
1 name1
2 name2
3 name3

Table: Rel

user1 user2
-- --
1 2
3 2
1 3

My goal is to be able to retrive it like this :

user1 user2
-- --
name1 name2
name3 name2
name1 name3

Excuse my bad terminalogy and english. The user1 and user2 are foreign keys from users.user_id and together makes composite key.

I am able to get one coloumn like so below

SELECT users.username
FROM users
JOIN rel ON rel.user1 = users.user_id

But when i try to get them together like displayed above with goal i dont manage to get it working at all. If anybody have any suggestions i would be really greatful

Answer Source

You should really "just try something" before asking. Try this:

SELECT u1.username user1, u2.username user2
FROM rel
JOIN users u1 ON rel.user1 = u1.user_id
JOIN users u2 ON rel.user2 = u2.user_id

A most important part to note is that you MUST use "table aliases" to differentiate between the first join and the second join to the same table. "u1" and "u2" are the aliases I chose for this example.