MySQL Where IN must Match all Values

Question

my sql script :

select
    pack_id 
from
    pack_product pp 
where
    pp.product_id in (2,1) 
group by
    pack_id

data example :

pack_id  | product_id
1        | 1
2        | 1
2        | 2

I want to return only pack_id 2 because match all values , but this script return pack_id 1 and 2.

Why would the pack_id not return where its product_id is 1 and you have pp.product_id in (2,1) which is like (pp.product_id =1 OR pp.product_id =2) — 丹尺爪工れ, Nov 03 '21 at 13:50
You can do it by doing a `group by` as you did and comparing the count(*) to the number of items in the `ANY` — Lajos Arpad, Nov 03 '21 at 14:37

ggordon · Answer 1 · 2021-11-04T16:15:48.923

1

Try using the having clause instead to apply your filter constraint on groups of pack_ids having all possible values with a case expression eg

Schema (MySQL v5.7)

CREATE TABLE pack_product (
  `pack_id` INTEGER,
  `product_id` INTEGER
);

INSERT INTO pack_product
  (`pack_id`, `product_id`)
VALUES
  ('1', '1'),
  ('1', '1'),
  ('2', '1'),
  ('2', '2');

Query #1

select
    pack_id 
from
    pack_product pp 
group by
    pack_id
having
    COUNT( DISTINCT CASE WHEN pp.product_id in (2,1) THEN pp.product_id END)=2;

pack_id
2

View working demo on DB Fiddle

edited Nov 04 '21 at 16:15

answered Nov 03 '21 at 13:54

ggordon

9,790
2
14
27

This will only work if there are no duplicate product_ids per pack_id, so make sure a unique constraint is on the two columns if going this route. – Briguy37 Nov 03 '21 at 14:17
@Briguy37 See demo with it working with duplicate product_ids [here](https://www.db-fiddle.com/f/gB43WTWPSVQvGK7PNpgUrH/1). The `>=2` ensures that it works with duplicates. If you have a different example, please share with an update of this fiddle – ggordon Nov 03 '21 at 14:19
Hi ggordon, here's the updated db fiddle with an example problem case ('1' should not be returned): https://www.db-fiddle.com/f/gB43WTWPSVQvGK7PNpgUrH/2 That case is almost certainly bad data, but without the explicit unique constraint on the columns it is possible to get the database in that state, so just wanted to mention it as a gotchya. – Briguy37 Nov 04 '21 at 14:27

score 0 · Answer 2 · answered Nov 03 '21 at 14:10

An IN clause matches ANY row that has any of the values supplied to the clause.

Instead you want a pack_id that has row entries with ALL of the values supplied, which is different and so an IN clause will not get you what you need.

One way to accomplish your goal is to inner join on pack_id and limit the joins and the root table to the product_ids you want, something like this:

select
    p1.pack_id 
from pack_product p1
  inner join pack_product p2 on p1.pack_id = p2.pack_id
where
    p1.product_id = 1 
    and p2.product_id = 2;

MySQL Where IN must Match all Values

2 Answers2