mysqlsqlcountaggregate-functionshaving-clause

SQL Query: Get those orders that have products greater than 1

Orders Table

psa_psofk is order ID and psa_prdfk is product ID. I want only those orders that have more than one product i.e I don't want order 1 and 5.

Solution

  • You can use group by and having:

    select psa_psofk 
from mytable 
group by psa_psofk 
having count(*) > 1

    This assumes no duplicates (psa_psofk, psa_prdfk). Else, you need to change the having clause to:

    having count(distinct psa_prdfk) > 1

    If you want entire rows, then one option uses exists:

    select t.*
from mytable t
where exists (
    select 1 
    from mytable t1 
    where t1.psa_psofk = t.psa_psofk and t1.psa_prdfk <> t.psa_prdfk
)