Sort result of a JOIN query based on the number of records in one of the tables

Asked

Viewed 451 times

1

Assuming I have two tables in my database (pedidos and pedidos_itens) how I can obtain data from requests ordered by orders containing more items?

I’ve tried to make a Right Join, but I don’t know what to use in the clause order by, since my goal is to sort by the number of records in the table pedidos_itens.

mysql sql join

  • How do you know which orders contain the most items? is a number in one of the fields of each entry or the amount of entries with a given ID?

  • Each row of the table pedidos_itens is an item, so you would have to sort the query by the number of rows in the table itens_pedidos

1 answer

3


Try it like this:

SELECT
  COUNT(pi.*) AS itens,
  p.id AS id,
  p.descricao AS desc
FROM pedido p
  INNER JOIN pedido_itens pi ON
    pi.id_pedido = p.id
GROUP BY id, desc
ORDER BY itens ASC

The idea is to give a COUNT of the quantity of items returned to the order, group the record data in the GROUP BY and a ORDER BY for COUNT.

Browser other questions tagged mysql sql join

You are not signed in. Login or sign up in order to post.