MySQL查询返回2个用户之间的对话

Question

我正在尝试在 2 个用户之间的对话中生成消息列表。有2个相关表:

收件箱

• id_msg
• 日期消息
• id_user_from
• id_user_to
• 留言

用户

• id_user
• 姓名

在此示例中，我是用户 ID 1 (Jon)，正在与用户 ID 2 (Anna) 聊天。

SELECT SQL_CALC_FOUND_ROWS
i.id_msg,
i.id_user_from AS from,
i.id_user_to AS to,
u.name,
i.message,
FROM inbox AS i
INNER JOIN user AS u ON (u.id_user = i.id_user_from OR u.id_user = i.id_user_to)
WHERE (i.id_user_from = 1 AND i.id_user_to = 2) OR (i.id_user_from = 2 AND i.id_user_to = 1)
    ORDER BY date_msg DESC

目前的问题是结果重复。我收到 2 个重复的 id_msg 值，每个值都链接到每个用户的姓名，例如:

id  | id_from   | id_to | name  | message
1   | 1         | 2     | Jon   | Hi Anna!
1   | 1         | 2     | Anna  | Hi Anna!
2   | 2         | 1     | Jon   | Hello Jon
2   | 2         | 1     | Anna  | Hello Jon

我应该收到这个:

id  | id_from   | id_to | name  | message
1   | 1         | 2     | Jon   | Hi Anna!
2   | 2         | 1     | Anna  | Hello Jon

有什么想法吗？谢谢!

Answer 1

如果只想显示from用户，则不需要在加入条件中匹配to用户；

SELECT SQL_CALC_FOUND_ROWS
    i.id_msg, i.id_user_from from_id, i.id_user_to to_id,
    u_from.name from_name, u_to.name to_name, i.message
FROM inbox AS i
INNER JOIN user AS u_from ON u_from.id_user = i.id_user_from
INNER JOIN user AS u_to   ON u_to.id_user   = i.id_user_to
WHERE (i.id_user_from = 1 AND i.id_user_to = 2) 
   OR (i.id_user_from = 2 AND i.id_user_to = 1)
ORDER BY date_msg DESC