-3

My question is basically an extension of this question - How to find if a list/set is contained within another list

I have the same data, but with more exact same products -

order_id | product_id
----------------------
1        | 222
1        | 555
2        | 333
2        | 222
2        | 555

I want to find the order(s) where the order has EXACTLY given product ids.

For example: If I search for 222 & 555, I should get order_id 1 as output since it only have those 2 exact product ids. And should not get order id 2 as output since it has an additional 333 product id.

Sunil Kumar
  • 622
  • 1
  • 12
  • 33
  • "The accepted answer will result in order_id = 2" but order_id=2 has 3 `product_id`'s....? – Luuk Oct 31 '20 at 13:45
  • The accepted answer of the referenced question output both order id 1 & 2. order id 2, is not required in my scenario as it is an EXACT match of product ids. – Sunil Kumar Oct 31 '20 at 14:35
  • `order_id=2` is not an exact match. It has 3 products and you seem to be looking for 2. this makes the question unclear, that makes people use the option "it is unclear" (which is a downvote) – Luuk Oct 31 '20 at 14:45

1 Answers1

1

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 ;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786