Hello. The problem is, I have a table ROOMS :

 id, slot_1, slot_2, slot_3, slot_4, slot_5

And there is also a USERS table:

 uid, name

How to make such a thing so that each slot_N is associated with USERS ?

The fact is that I do this inside phpMyAdmin, there I don’t understand how to establish connections at all, but it seems like I did everything with indices, but how do I prescribe a LEFT JOIN during the selection?

  • I did it like this, but this is wrong: SELECT * FROM rooms LEFT JOIN users ON rooms.slot_1 = users.id LEFT JOIN users ON rooms.slot_2 = users.id LEFT JOIN users ON rooms.slot_3 = users.id - Shevsky
  • I have slot_N digital IDs corresponding to the uid of USERS. I want to select the entire USERS content for each slot_N. - Shevsky
  • For each slot_N, i.e. if there are 5 different user id in the record, then you want to see in the selection of 5 rooms_id, ползователь такой-то records, the rooms_id, ползователь такой-то or one record with 5 names, id and what else does the user have? PS In general, you have a base in a very strange form, many columns with user id clearly violate even the initial normal forms. Maybe you should think about a different base structure - Mike
  • And so: SELECT * FROM rooms r LEFT JOIN users u1 ON r.slot_1 = u1.id LEFT JOIN users u2 ON r.slot_2 = u2.id LEFT JOIN users u3 ON r.slot_3 = u3.id - androschuk
  • I agree with @Mike should be something like this: room(id, slot); user(id, name); user_room(room_id, user_id room(id, slot); user(id, name); user_room(room_id, user_id room(id, slot); user(id, name); user_room(room_id, user_id then and with a change in the number of rooms there is no need to change the structure of the base - androschuk

1 answer 1

You can do the following:

 SELECT r.id AS id, s1.name AS slot_1, s2.name AS slot_2, s3.name AS slot_3, s4.name AS slot_4, s5.name AS slot_5 FROM rooms AS r LEFT JOIN users AS s1 ON r.slot_1 = s1.ui‌​d LEFT JOIN users AS s2 ON r.slot_2 = s2.ui‌​d LEFT JOIN users AS s3 ON r.slot_3 = s3.ui‌​d LEFT JOIN users AS s4 ON r.slot_4 = s4.ui‌​d LEFT JOIN users AS s5 ON r.slot_5 = s5.ui‌​d;