mysql - 两次左连接

Question

我有两张 table

TBL USERS
    user_id
    user_name

TBL MESSAGES
    message_id
    message_from_id
    message_to_id
    message_text

我使用简单的 JOIN 来检索变量

SELECT *
FROM messages
LEFT JOIN users ON user_id = msg_to
WHERE msg_id = $msg_id

$user_name = $row['user_name'];
$message_text = $row['message_text'];

如何从同一个表中获取2个变量？

$message_from = $row['user_name'];
$message_to = $row['user_name'];

我期望这样的结果:

MSG ID     FROM     TO     TEXT
1          Paul     Mark   Blah

Answer 1

SELECT message_text, 
   to_users.user_name as to_user, 
   from_users.user_name as from_user
FROM messages
LEFT JOIN users as to_users ON user_id = message_to_id
LEFT JOIN users as from_users ON user_id = message_from_id
WHERE message_id = $msg_id;

$message_from = $row['from_user'];
$message_to = $row['to_user'];
$message_text = $row['message_text'];

顺便说一句，如果您使用 PHP，我建议您养成使用准备好的语句的习惯，而不是在字符串中使用变量，例如 $msg_id。这可以帮助保护您免受 SQL 注入(inject)攻击。