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.
Asked
Active
Viewed 599 times
-1
-
Most people here want sample table data _and the expected result_ as formatted text, not images. – jarlh Nov 12 '20 at 16:39
-
@jarlh with notable exceptions :-( – Strawberry Nov 12 '20 at 18:55
2 Answers
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
)
GMB
- 216,147
- 25
- 84
- 135
-
But I don't want to group my orders. I want exact same table without first and last row – artsnr Nov 12 '20 at 16:42
-
0
Here is query that returns number of orders those have products greater then 1
SELECT * FROM orders o INNER JOIN products p ON o.product_id = p.id
WHERE o.product_id IN(SELECT o.product_id FROM orders o GROUP BY o.product_id
HAVING COUNT(o.product_id) > 1)
Aamir Shaikh
- 47
- 1
- 6