How can I do two joins and showing nulls where there is not data

Question

I have 3 tables as such

+----+---------+
| id | message |
+----+---------+
| 1  | sup     |
+----+---------+
| 2  | hello   |
+--------------+

+------------+----------+
| message_id | comments |
+------------+----------+
| 1          | hi       |
+------------+----------+ 
| 1          | bye      |
+------------+----------+ 

+------------+-------+
| message_id | likes |
+------------+-------+
| 1          | sup   |
+------------+-------+
| 2          | hello |
+------------+-------+

And I'm looking for something in the end like this

+----+---------+------------+----------+------------+-------+
| 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 |
+----+---------+------------+----------+------------+-------+

I have 2 joins like

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

But this is returning all the fields populated. I want the fields to be not be populated where they do not exist. Is this possible?

EDIT: To address peoples comments

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   |
+----+---------+------------+----------------------+------------+-----------+

I agree that likes should be an integer but this data is a mock data for something I just made up.

You should consider what you want to happen if a message has multiple likes and comments -- probably in another question. — Gordon Linoff, Jul 18 '16 at 14:52
Your third table with the `likes` attribute: is the data correct? I was expecting a tally/count (integer) here. — onedaywhen, Jul 18 '16 at 14:54

score 2 · Accepted Answer · edited May 23 '17 at 11:51

2

You just need to use a LEFT JOIN instead of an 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

More information on the different types of joins can be found here:

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

edited May 23 '17 at 11:51

Community

1
1

answered Jul 18 '16 at 14:47

Siyual

16,415
8
44
58

score 0 · Answer 2 · answered Jul 18 '16 at 14:49

0

Maybe try this and see if it helps;

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

For further reading check this out; http://www.w3schools.com/Sql/sql_join_left.asp

answered Jul 18 '16 at 14:49

NHier1992

23
7

score 0 · Answer 3 · answered Jul 18 '16 at 15:02

0

Yes, Left Joins will keep all rows in the Left table or first table in that join expression, so this is probably what you want:

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

answered Jul 18 '16 at 15:02

Brett Drake

301
3
12

How can I do two joins and showing nulls where there is not data

3 Answers3