对于项目，我必须查找特定人员已发送的所有消息。所以，说我有这些表：
人
person_id | first_name | last_name
信息
message_id | sender_id | receiver_id | message
sender_id和Receiver_id都引用person_id。我必须显示发件人的名字和姓氏，收件人的名字和姓氏以及消息。我特别在显示发件人姓名和收件人姓名时遇到麻烦。
我已经尝试过各种搜索来解决我的问题，但似乎没有任何效果或与我的问题真正相关。我已经看到一些解决方案使用UNION，但是我还没有在课堂上学到这一点。这是我到目前为止所拥有的：
SELECT p.first_name AS "Sender First Name",
p.last_name AS "Sender Last Name",
p.first_name AS "Reciever First Name",
p.last_name AS "Reciever Last Name",
m.message_id AS "Message ID",
m.message AS "Message",
m.send_datetime AS "Message Timestamp"
FROM message m, person p
WHERE m.sender_id = 1
AND m.sender_id = p.person_id;
这给了我：
+-------------------+------------------+---------------------+--------------------+------------+--------------------------------------------+---------------------+
| Sender First Name | Sender Last Name | Reciever First Name | Reciever Last Name | Message ID | Message | Message Timestamp |
+-------------------+------------------+---------------------+--------------------+------------+--------------------------------------------+---------------------+
| Michael | Phelps | Michael | Phelps | 1 | Congrats on winning the 800m Freestyle! | 2016-12-25 09:00:00 |
| Michael | Phelps | Michael | Phelps | 4 | Thanks! You're the greatest sprinter ever | 2016-12-25 09:04:00 |
| Michael | Phelps | Michael | Phelps | 5 | Good luck on your race | 2016-12-25 09:05:00 |
+-------------------+------------------+---------------------+--------------------+------------+--------------------------------------------+---------------------+
因此，发件人的名字是正确的，但收件人的名字是不正确的。如果我将最后一行更改为
AND m.receiver_id = p.person_id;
那么我会得到正确的接收方名称，但没有正确的发送方名称。
您需要两个联接。那就是JOIN！
SELECT ps.first_name AS "Sender First Name",
ps.last_name AS "Sender Last Name",
pr.first_name AS "Receiver First Name",
pr.last_name AS "Receiver Last Name",
m.message_id AS "Message ID",
m.message AS "Message",
m.send_datetime AS "Message Timestamp"
FROM message m JOIN
person ps
ON m.sender_id = ps.person_id JOIN
person pr
ON m.receiver_id = pr.person_id
WHERE m.sender_id = 1;
笔记：
FROM条款。JOIN语法。FROM。NULL，请使用LEFT JOIN代替JOIN。本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句