LEFT JOIN Com GROUP BY
I am using PHP and MySQL, I have two tables in the database:
POSTAGEM
id
----
1
3
13
CURTIDA
id | id_usuario | id_postagem
-----------------------------
1 | 1 | 1
7 | 12 | 1
What I can not is the following: I want to give a select in the posts and next to it say qntas likes each one has, I did like this:
SELECT postagem.id AS idPostagem, COUNT(curtida.id) AS curtidasTotais
FROM postagem
LEFT JOIN curtida ON curtida.id_postagem = postagem.id
GROUP BY curtida.id_postagem
But it ends up not listing right the posts it has not liked... in this example it does not list post 13 (which has no likes) but lists post 3 (which also has no likes)
Here you can see this error in action: http://sqlfiddle.com/#! 9 / 9c0d7c/1
2 answers
The problem is that you are giving grup by by the value of the table that is selected by the left join. To get it right just invert:
SELECT postagem.id AS idPostagem, COUNT(curtida.id) AS curtidasTotais
FROM postagem
LEFT JOIN curtida ON postagem.id = curtida.id_postagem
GROUP BY postagem.id
Grouping by the id of the original table.
You can check the result here:
In this case as you only want the amount of likes you can use a subquery to fetch the count:
SELECT p.*,
(SELECT COUNT(1)
FROM curtida c
WHERE c.id_postagem = p.id) AS curtidas
FROM postagem p
See working on SQL Fiddle.