My problem is as follows. I need a query to order a list of articles for the amount of comments that each one has. I have a Table
Articulo(id,titulo,contenido)
and another
Comentario(id,id_articulo,comentario,id_usuario)
Emphasize that all articulos do not have comments, therefore if they are ordered in descending order, those articulos that do not have comments should appear at the end.
I think this is what you need
SELECT
id,titulo,contenido, COUNT(*) suma
FROM
articulos
INNER JOIN
comentarios ON articulos.id = comentarios.id_articulo
GROUP BY id,titulo,contenido
ORDER BY suma DESC
The Join method helps you to consult two tables that are related to each other (As in the solution) you make a count (*) to add the number of the record that returns the query grouping by the fields of the table articles since can be repeated if the article has more than one comentrio and you end up ordering in descending order for the sum to take you out the articles with more comments
You can join the article table by means of a left join with the comment table, in this way, after making a count and grouping, you can order based on that total, in the case of the query that you order, sort it in descending order according to the total number of comments and then if the totals are equal, sort A and Z according to the title. Making the left if you do not find comments puts the value at 0.
SELECT a.id, a.titulo, a.contenido, count(c.id) as totalComentario
from articulo a left join comentario c on a.id = c.id_articulo
group by a.id order by totalComentario desc, a.id asc
I leave a link where all the types of join that you can do with examples are explained link