With a mysql query?

Question

MySQL

Please help me with what seems to be a simple request.
There is a message table: message_id, message_from, message_to. Here message_from, message_to are the user IDs from whom and to whom this message is.
There is a table of users user_id, login.
How to build a query to simultaneously display message_id, login from whom, login to whom?
If it would be necessary to display only one message_id and login, it is clear what is done through LEFT JOIN users ON user_id = message_from. And how to display the logins of both users at once?

Answer 1

Make two joins
SELECT m.message_id, u1.login, u2.login
FROM messages m
LEFT JOIN users u1 ON m.message_from=u1.user_id
LEFT JOIN users u2 ON m.message_to=u2.user_id

Answer 2

select users_from.login as login_from, users_to.login as login_to…
LEFT JOIN users as users_from ON users_from.user_id = messages.message_from,
LEFT JOIN users as users_to ON users_to.user_id = messages.message_to,

Answer 3

Make 2 requests.
The first selects a message, including the IDs of the users associated with it.
The second selects both users by ID.
In the case of using JOIN, a change in any table entails flushing the query cache in the database.
In the case of two queries, for example, changes in the messages table will not reset the query cache on the users table, and vice versa.