SELECT sort by quantity of another SELECT

Question

SELECT sort by quantity of another SELECT

Navigation

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

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 it wanted, it only lists the users that have product registered and that the status of this product equals 3.

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

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 that it is not the right ...

mysql sql sql-select order-by

asked by anonymous 21.09.2015 / 20:00

2 answers

0

I would change the way to 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

21.09.2015 / 20:10

Picking up top div ID to click how to leave the outer div invisible (hidden), but its visible interior?

score 2 · Accepted Answer

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.