CODEWITHSUNDEEP
mysqlsql
user2807536
user2807536

Reputation: 93

Many foreign keys to one table

I'm not sure even how to google it so I decided to ask here. I have two tables in my MySQL database:

Table: users

 user_id | user_name | user_surname 
---------+-----------+-------------
 111     | Tom       | Hanks
 112     | Martin    | Dubravka     
 113     | Gary      | Muller
 114     | Grzegorz  | Kowalewski
 115     | Chris     | Anderson
 116     | John      | Smith

and

Table: best_users

 id    | best_user_1 | best_user_2 | best_user_3 | best_user_4 | best_user_5 | best_user_6          
-------+-------------+-------------+-------------+-------------+-------------+-------------
 1     |  115        |  114        |  112        |  116        |  111        |  113

best_user_id field is the Foreign Key to field user_id in users table.

What I would like to achieve is to get (best in one query):

 id    | best_user_1  | user_name1 | user_surname1 | best_user_2 | user_name2 | user_surname2 | best_user_3 | ...
-------+--------------+------------+---------------+-------------+------------+---------------+-------------+----
 1     |  115         | Chris      | Anderson      | 114         |  Grzegorz  | Kowalewski    | 112         | ...

Is it possible in one query in pure SQL? Of course I could get best_users first and then get user_name based on best_user_x field. Should be it done in some kind of JOIN?

Regards

M

Upvotes: 0

Views: 129

Answers (1)

Nick
Nick

Reputation: 147286

I hope you like typing... you need to JOIN to the users table 6 times, once for each best_user:

SELECT id,
       best_user_1,
       u1.user_name AS user_name1,
       u1.user_surname AS user_surname1,
       ...
       best_user_6,
       u6.user_name AS user_name6,
       u6.user_surname AS user_surname6
FROM best_users
JOIN users u1 ON u1.user_id = best_users.best_user_1
...
JOIN users u6 ON u6.user_id = best_users.best_user_6

Demo on dbfiddle

Upvotes: 2

Related Questions