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

Question

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.

Answer 1

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
)