-1

I have a table similar to the one shown in snip. I want to Query all the Facility_name and Product_Id for which all 'Week' exist i.e. 1,2,3

enter image description here

Desired output would be only 'A' Faility_Name and respective Product_Id, as for them we have all 3 Weeks available.

0
SELECT Facility_name 
FROM source_table
GROUP BY Facility_name 
HAVING COUNT(DISTINCT `Week`) = ( SELECT COUNT(DISTINCT `Week`) 
                                  FROM source_table )

and

SELECT *
FROM source_table 
JOIN ( SELECT Facility_name 
       FROM source_table
       GROUP BY Facility_name 
       HAVING COUNT(DISTINCT `Week`) = ( SELECT COUNT(DISTINCT `Week`) 
                                         FROM source_table ) ) subquery USING (Facility_name)

If your MySQL version is 8+ then move subqueries to CTE.

| improve this answer | |
  • Tried the 1st one but got only 1 record i.e. the very first one – user13325679 May 28 at 20:52
  • @user13325679 1st query returns only Facility_name which matches. 2nd returns all separate records which's Facility_name matches. – Akina May 29 at 5:03
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.