如何从其他表中获得发送者和接收器名称

Question

我正在尝试为PHP做一个简单的消息类。我的messages表结构如下：

id, sender_id, receiver_id, msg, date

我想从users表中获取发件人和接收器用户名。我该如何在一个查询中进行操作？

我期望的示例输出：

sender, sender_id, receiver, receiver_id, msg, date

Answer 1

您必须两次与用户加入消息，以获取所需的结果。尝试以下查询：

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