mysql – Query record when N values of a column are present


This is the 4th question I’ve ran across in a month about querying parents (facility_name) for containing, at least, a specific sub-set of tags ( Week ).

For reference, Here is one of the posts on DBA SE with my answer.

The solution:

  1. Find all the required matches
  2. ensure that you have the correct cardinality.

This question required COUNT(distinct) instead of COUNT(*) (as shown in the other post).

Your schema was simple enough to put into DB Fiddle to validate.

Schema (MySQL v8.0)

create table source_table ( Facility_name varchar(1), product_id varchar(2), `Week` int);

insert into source_table values
 ('A', 'A1', 1),
 ('A', 'A1', 1),
 ('A', 'A2', 2),
 ('A', 'A3', 3),
 ('B', 'B1', 1),
 ('B', 'B2', 2),
 ('C', 'B3', 3),
 ('C', 'C1', 1),
 ('D', 'D1', 2);

Query #1

with query_these_weeks as (
  select 1 as `Week` union all
  select 2 as `Week` union all
  select 3 as `Week`
)
select Facility_name
from source_table as a
  join query_these_weeks as b on a.`Week` = b.`Week`
group by Facility_name
having count(distinct a.`Week`) = (select count(*) from query_these_weeks);
| Facility_name |
| ------------- |
| A             |

View on DB Fiddle