SELECT sort by the quantity of another SELECT

Asked

Viewed 223 times

1

I have two tables in MYSQL:

account:

id   apelido_usuario
1    caio
2    manoel
3    josé

product

idconta status
1       3
1       3
1       1
2       3
3       2

I made a SELECT like this:

SELECT * FROM conta 
WHERE apelido_usuario <> ''
AND conta.id IN (SELECT produto.idconta FROM produto WHERE produto.status = '3');

It worked as wanted, it only lists users who have registered product and that the status of this product is equal to 3.

Now I need an ORDER BY, which is ordered by the user who has more products with the status = '3' in front, how to do?

I tried something like this:

SELECT * FROM conta 
WHERE apelido_usuario <> ''
AND conta.id IN (SELECT produto.idconta FROM produto WHERE produto.status = '3')
ORDER BY SUM(conta.id IN (SELECT produto.idconta FROM produto WHERE produto.status = '3')) DESC;

But I realized it’s not right...

mysql sql select-sql order-by

  • Just for the record, my query is much more complex than this, it has some LEFT JOIN, but that I omitted because I don’t think it comes to the case.

2 answers

2


Use a left Join to relate the 2 tables and make a ORDER BY

SELECT * FROM conta  C 
LEFT JOIN produto P ON P.idconta = C.id and  p.status = '3'
WHERE apelido_usuario <> ''
AND conta.id IN (SELECT produto.idconta FROM produto WHERE produto.status = '3')
ORDER BY P.idconta

I hope to help.

  • But STATUS = 3?

  • I adjusted the response to validate the Status.

  • It is possible this LEFT JOIN disturb another that already exists in my query?

  • No. 'Cause you’re wearing LEFT JOIN. You can use traqnuil.

0

I would change a little the way to make the query: 

SELECT conta.id,
           conta.apelido_usuario, 
           count(produto.*) QtdProdutos

    FROM conta
    INNER JOIN produto
    ON produto.idconta = conta.id


    WHERE conta.apelido_usuario <> ''
    AND produto.status = '3'

    GROUP BY  conta.id,
              conta.apelido_usuario 

    ORDER BY QtdProdutos

Browser other questions tagged mysql sql select-sql order-by

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