如何从另一个表中获取发件人和收件人姓名[关闭]

Question

I'm trying to make a simple Message class for PHP. My messages table structure is as follows:

id, sender_id, receiver_id, msg, date

I want to get sender and receiver username from users table. How can I do it in a single query?

Example output I expect:

sender, sender_id, receiver, receiver_id, msg, date

Answer 1

you must join messages with users two times to get the results you want. try the following query:

select distinct sender.username as senderName, sender.user_id as sender_id,
                receiver.username as receiverName, receiver.user_id as receiver_id, 
                msg.msg_body, msg.date 
from messages msg inner join users sender on msg.sender_id = sender.id
                  inner join users receiver on msg.receiver_id = receiver.id