You can still use having
, but test for each product -- and for the absence of any other product:
SELECT o.order_id
FROM orders o
GROUP BY o.order_id
HAVING SUM( product_id = 222 ) > 0 AND
SUM( product_id = 555 ) > 0 AND
SUM( product_id NOT IN (222, 555) ) = 0 ;
Note that this uses the MySQL shorthand, where boolean expressions are treated as numbers with 1
for true and 0
for false. The standard syntax is:
HAVING SUM( CASE WHEN product_id = 222 THEN 1 ELSE 0 END) > 0 AND
SUM( CASE WHEN product_id = 555 THEN 1 ELSE 0 END ) > 0 AND
SUM( CASE WHEN product_id NOT IN (222, 555) THEN 1 ELSE 0 END ) = 0 ;
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…