Efficient way to join multiple columns to the same column? – SQL

I have a bunch of tables combined together, which each have a column containing some form of user_id. This leads to 12 user_id columns in total.

I want to join each of these user_id columns with a user_id column in a mapping table in order to retrieve the username for each of these user ids.

So (assuming I have 5 user id columns),

Input:

My Combined Tables Result:
t1.user_id  t2.user_id  t3.user_id  t4.user_id  t5.user_id
1           2           3           4           5

Mapping Table:
user_id     username
1           A
2           B
3           C
4           D
5           E

Output:

t1.username  t2.username  t3.username  t4.username  t5.usernamne
A            B            C            D            E

My code looks something like:

SELECT m1.username, m2.username, m3.username, m4.username, m5.username
FROM {join logic for 5 tables here}
JOIN mapping m1
ON t1.user_id = m1.user_id
JOIN mapping m2
ON t2.user_id = m2.user_id
JOIN mapping m3
ON t3.user_id = m3.user_id
JOIN mapping m4
ON t4.user_id = m4.user_id
JOIN mapping m5
ON t5.user_id = m5.user_id

I’m realizing this is extremely inefficient, especially for 12 columns which would mean 12 JOINs. Is there a better or faster way to do this? Thanks!

Answer

You might find it easier to use a correlated subquery for each username, especially where you have to implement many columns as it’s easier to cut n paste!

Something like:

select 
 (select Username from Mapping m where m.UserId = t.UserId1) Username1,
 (select Username from Mapping m where m.UserId = t.UserId2) Username2,
 (select Username from Mapping m where m.UserId = t.UserId3) Username3 etc
from InputTable t