php - MySQL 通过匹配它们的 id 来组合两个表字段

Question

例如我创建了两个表。

表一:t5zgu_property_message

msg_from msg_to subject message

57       42     xxxxx   xxxxxx
57       42     xxxxx   xxxxxx
57       42     xxxxx   xxxxxx
42       42     xxxxx   xxxxxx

表二:t5zgu_users

id username

42 Jack
57 Rocky

我想要这样的输出:

msg_from msg_to subject message msg_from  msg_to

57       42     xxxxx   xxxxxx  Rocky     Jack
57       42     xxxxx   xxxxxx  Rocky     Jack
57       42     xxxxx   xxxxxx  Rocky     Jack
42       42     xxxxx   xxxxxx  Jack      Jack

我当前的查询是:

SELECT 
    t5zgu_property_message.id,
        t5zgu_property_message.msg_from,
        t5zgu_property_message.msg_to,
        t5zgu_property_message.subject,
        t5zgu_property_message.message,
        t5zgu_users.username as msg_from
FROM 
    t5zgu_property_message,
        t5zgu_users
WHERE
    t5zgu_property_message.msg_from = t5zgu_users.id

ORDER BY t5zgu_property_message.id DESC

此查询与 msg_from 完美配合并获得正确的输出，但我不知道如何为 msg_to 编写。

有什么想法或建议吗？谢谢。

Answer 1

您只需再次加入users 表:

SELECT 
    t5zgu_property_message.id,
        t5zgu_property_message.msg_from,
        t5zgu_property_message.msg_to,
        t5zgu_property_message.subject,
        t5zgu_property_message.message,
        t5zgu_users.username as msg_from,
        t5zgu_users2.username as msg_to
FROM 
    t5zgu_property_message,
    t5zgu_users,
    t5zgu_users t5zgu_users2
WHERE
    t5zgu_property_message.msg_from = t5zgu_users.id
    AND
    t5zgu_property_message.msg_to = t5zgu_users2.id

ORDER BY t5zgu_property_message.id DESC

或者使用 JOIN 语法同样的事情:

SELECT 
    t5zgu_property_message.id,
        t5zgu_property_message.msg_from,
        t5zgu_property_message.msg_to,
        t5zgu_property_message.subject,
        t5zgu_property_message.message,
        t5zgu_users.username as msg_from,
        t5zgu_users2.username as msg_to
FROM 
    t5zgu_property_message
    JOIN t5zgu_users ON t5zgu_property_message.msg_from = t5zgu_users.id
    JOIN t5zgu_users t5zgu_users2 ON t5zgu_property_message.msg_to = t5zgu_users2.id
ORDER BY t5zgu_property_message.id DESC