Retrieve two colums of data with one join

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.

Answer

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;

Fiddle