I'm trying to display a list of paired people.
I have a link table looking like the following:
| id | user_id_one | user_id_two |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 3 | 4 |
I wanna retrieve the names linked to these id's from the main user table looking like:
| id | name |
|---|---|
| 1 | jef |
| 2 | kevin |
| 3 | mike |
| 4 | sam |
I just can't seem to figure it out. I tried the following:
SELECT name
FROM users a
INNER JOIN link_table b ON a.id = b.user_id_one
INNER JOIN link_table b ON a.id = b.user_id_two
This won't fetch anything. When commenting out one of the two joins I do get values. So my question is: How do I fetch two columns of data from the same table that they are linked to?
The big thing is, I just don't know what to google since I am fairly new to SQL. I'm thinking I need a sub query in the select part to make it two separate columns, but I wouldn't know where to start.
CodePudding user response：
You need to join the users table twice to the link table, not the opposite:
select l.id,
user_id_one,
u1.name as user_id_one_name,
user_id_two,
u2.name as user_id_two_name
from link_table l
inner join users u1 on l.user_id_one = u1.id
inner join users u2 on l.user_id_two = u2.id;