LEFT JOIN WITH GROUP BY

Question

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’t do is this: I want to select the posts and next to it say how many likes each one has, I did so:

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 he ends up not listing the posts right that has not liked... in this example does not list the post 13 (that has not liked) but lists the 3 (that also has not liked)

Here you can see this error in action: http://sqlfiddle.com/#! 9/9c0d7c/1

Answer 1

the Problem is that you are giving Grup by the value of the table that is selected by left Join. To hit 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 original table id.

You can check the result here:

http://sqlfiddle.com/#! 9/9c0d7c/9/0

Answer 2

In this case as you just want the amount of likes you can use a subquery to get 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.