Accounts as many ids equal and make a result table larger to smaller

Question

Accounts as many ids equal and make a result table larger to smaller

Navigation

#1 by (0 votes)

0

I'm doing an exercise that asked:

Create a postagem table where you will have id , titulo_postagem , and also create a table named comentarios where you will have id , id_postagem , comentario .

After the tables created you are asked to return the posts with more comments.

I was running a query as follows, but to no avail:

$selPostMaisComentados = $conn->prepare("SELECT * FROM postagem AS p INNER JOIN comentarios AS c ON c.id_postagem = p.id");
$selPostMaisComentados->execute();

Table postagem :

id   titulo
1    Poste aqui...
2    Etc...
3    fala mano

Table comentarios :

id id_postagem  comentario
1  1            oi
2  1            fala ae
3  2            iae
4  1            to inventando os comentario......
5  2            huhu
6  3            aiaiai

php

asked by anonymous 06.07.2018 / 02:46

1 answer

React Native Compile Update Separate javascript object into groups with 4 items in the click event!

score 0 · Answer 1

You can make a subselect to count the number of comments per post and then sort by descending order

  SELECT p.id
        ,p.titulo_postagem
        ,a.total
    FROM postagem AS p 
    JOIN ( SELECT COUNT(c.*) total, c.id_postagem
             FROM comentarios AS c 
            WHERE c.id_postagem = p.id
            GROUP BY c.id_postagem
          ) a
    WHERE a.id_postagem = p.id
     ORDER BY a.total  DESC   ;

PS: I did not test the code