Join, Group By and Sum in MySQL

Question

Hello,

I have the following three tables in a MySQL database in order to give ratings to comments of users:

Users:

id    name
-----------
1     Smith
2     Brown


Comments:

id    user_id    post_id    comment
-----------------------------------
1     2          1          Test 1
2     1          1          Test 2
3     1          1          Test 3


Scores:

id    user_id    comment_id    score
------------------------------------
1     1          1             1

Now I want to select all the comments for post_id = 1, plus the username and the sum of all the scores on that specific comment. At first it looks very simple, I came up with this query:

SELECT users.name, comments.comment, SUM(scores.score) AS score
FROM comments
LEFT JOIN users ON users.id = comments.user_id
LEFT JOIN scores ON scores.comment_id = comments.id
WHERE comments.post_id = 1
GROUP BY scores.comment_id

It seems to work, but when there isn't a score for a specific comment, the comment doesn't show up, because MySQL can't GROUP BY NULL, I guess. So, is there any way to include those unrated comments? Like this:

Query result:
name     comment    score
-------------------------
Brown    Test 1     1
Smith    Test 2     0
Smith    Test 3     0

Answer 1

You could try grouping on comments.id instead perhaps?