我有两张桌子,
orders
和
ordered_products
。
ORDERS
|ORDERS_ID|CUSTOMER NAME|...
|1 |PIPPO |...
|2 |PLUTO |...
ORDERED PRODUCTS
|ORDERED_ID|ORDERS_ID|PRODUCT |PRODUCT_TYPE|...
|1 |1 |ProdottoA| 1 |...
|2 |1 |ProdottoB| 2 |...
|3 |1 |ProdottoC| 1 |...
|4 |2 |ProdottoD| 2 |...
我需要两个查询,第一个是选择所有具有
至少
一个类型为1的产品,第二个选择具有所有类型为1产品的所有订单。
对于第一个问题,我用以下查询解决了:
select distinct orders_id from ORDERS o left join ORDERED_PRODUCTS op on (o.orders_id=op.orders_id) where op.product_type = '1'
但我找不到第二个问题的解决方案。
找到解决方案!我曾经:
select distinct orders_id from ORDERS o left join ORDERED_PRODUCTS op on (o.orders_id=op.orders_id)
where
(select count(ordered_id) from ordered_products op where op.orders_id = o.orders_id)
=
(select count(ordered_id) from ordered_products op where op.orders_id = o.orders_id and op.orders_products_categorizzazione='1')
谢谢你的帮助
