LEFT JOIN with GROUP BY

Question

LEFT JOIN with GROUP BY

Navigation

#1 by (2 votes)
#2 by (1 votes)

2

I'm 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 do is the following: I want to give a select in the posts and next to it say how many tans each 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 does not list the posts that he has not tanned right ... in this example he does not list the post 13 (which has no tanned) but list 3 (which also has no tanned)

Here you can see this error in action: link

mysql group-by

asked by anonymous 27.09.2017 / 19:16

2 answers

1

In this case as you only want the amount of tanned you can use a subquery to search the count:

SELECT p.*,
       (SELECT COUNT(1)
          FROM curtida c
         WHERE c.id_postagem = p.id) AS curtidas
  FROM postagem p

See working in SQL Fiddle .

27.09.2017 / 19:26

Delphi - Executable with different behavior on different machines Verify that the entry is an integer

score 2 · Accepted Answer

The problem is that you are giving grup by the value of the table that is selected by the left join. To get it right just reverse:

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 id of the original table.

You can check the result here:

link