Tips to make only One query?

Question

Say I have the following tables:

users
-----
id
username


messages
-----
id
sender (id user)
recipient (id user)
msg

In a single query, how can I get the name of the sender, the name of the recipient, and the message?

I would basically need to join the users table twice, but not sure how that would be possible..

Answer 1

SELECT
   s.username as sender,
   r.username as recipient,
   msg
FROM
   messages m join users s on (m.sender = s.id)
   join users r on (m.recipient = r.id)