如何进行两次连接并在没有数据的地方显示null

Question

我有3个这样的表

+----+---------+
| id | message |
+----+---------+
| 1  | sup     |
+----+---------+
| 2  | hello   |
+--------------+
+------------+----------+
| message_id | comments |
+------------+----------+
| 1          | hi       |
+------------+----------+ 
| 1          | bye      |
+------------+----------+ 
+------------+-------+
| message_id | likes |
+------------+-------+
| 1          | sup   |
+------------+-------+
| 2          | hello |
+------------+-------+

我正在寻找像这样的东西

+----+---------+------------+----------+------------+-------+
| id | message | message_id | comments | message_id | likes |
+----+---------+------------+----------+------------+-------+
| 1  | sup     | 1          | hi       | null       | null  |
+----+---------+------------+----------+------------+-------+
| 1  | sup     | 1          | bye      | null       | null  |
+----+---------+------------+----------+------------+-------+
| 1  | hello   | null       | null     | 1          | sup   |
+----+---------+------------+----------+------------+-------+
| 1  | hello   | null       | null     | 2          | hello |
+----+---------+------------+----------+------------+-------+

我有两个像一样的加入

select * from message join comments on id = comments.message_id join likes on id = likes.message_id

但这是返回所有已填充的字段。我希望字段不在不存在的地方填充。这可能吗？

编辑：为了解决人们的意见

mysql> select * from message join comments on id = comments.message_id join likes on id = likes.message_id;
+----+---------+------------+----------------------+------------+-----------+
| id | message | message_id | comment              | message_id | comment   |
+----+---------+------------+----------------------+------------+-----------+
|  1 | hello   |          1 | hello                |          1 | what      |
|  1 | hello   |          1 | hello                |          1 | what what |
|  2 | two     |          2 | bye                  |          2 | what ok   |
|  1 | hello   |          1 | hello what what      |          1 | what      |
|  1 | hello   |          1 | hello what what      |          1 | what what |
|  1 | hello   |          1 | hello what what what |          1 | what      |
|  1 | hello   |          1 | hello what what what |          1 | what what |
+----+---------+------------+----------------------+------------+-----------+
mysql> select * from message left join comments on id = comments.message_id left join likes on id = likes.message_id;
+----+---------+------------+----------------------+------------+-----------+
| id | message | message_id | comment              | message_id | comment   |
+----+---------+------------+----------------------+------------+-----------+
|  1 | hello   |          1 | hello                |          1 | what      |
|  1 | hello   |          1 | hello what what      |          1 | what      |
|  1 | hello   |          1 | hello what what what |          1 | what      |
|  1 | hello   |          1 | hello                |          1 | what what |
|  1 | hello   |          1 | hello what what      |          1 | what what |
|  1 | hello   |          1 | hello what what what |          1 | what what |
|  2 | two     |          2 | bye                  |          2 | what ok   |
+----+---------+------------+----------------------+------------+-----------+

我同意赞应该是一个整数，但这个数据是我刚刚编造的东西的模拟数据

Answer 1

您只需要使用LEFT JOIN而不是INNER JOIN:

SELECT     *
FROM       message  M
LEFT JOIN  comments C  ON M.id = C.message_id
LEFT JOIN  likes    L  ON M.id = L.message_id

关于不同类型联接的更多信息可以在这里找到：

什么'；INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN之间的区别是什么？

Answer 2

也许试试这个，看看它是否有帮助；

select * from message 
left join comments on id = comments.message_id 
left join likes on id = likes.message_id

如需进一步阅读，请查看此信息；http://www.w3schools.com/Sql/sql_join_left.asp

Answer 3

是的，Left Joins将保留Left表中的所有行或该联接表达式中的第一个表，因此这可能是您想要的：

选择*FROM消息LEFT JOIN comments ON id=comments.message_idLEFT在id=likes.message_id 上加入点赞