SQL Select a group when attributes match at least a list of values

Question

Given a table with a (non-distinct) identifier and a value:

| ID | Value |
|----|-------|
|  1 |     A |
|  1 |     B |
|  1 |     C |
|  1 |     D |
|  2 |     A |
|  2 |     B |
|  2 |     C |
|  3 |     A |
|  3 |     B |

How can you select the grouped identifiers, which have values for a given list? (e.g. ('B', 'C'))

This list might also be the result of another query (like SELECT Value from Table1 WHERE ID = '2' to find all IDs which have a superset of values, compared to ID=2 (only ID=1 in this example))

Result

| ID |
|----|
|  1 |
|  2 |

1 and 2 are part of the result, as they have both A and B in their Value-column. 3 is not included, as it is missing C

Thanks to the answer from this question: SQL Select only rows where exact multiple relationships exist I created a query which works for a fixed list. However I need to be able to use the results of another query without changing the query. (And also requires the Access-specific IFF function):

SELECT ID FROM Table1
GROUP BY ID 
HAVING SUM(Value NOT IN ('A', 'B')) = 0 
AND SUM(IIF(Value='A', 1, 0)) = 1 
AND SUM(IIF(Value='B', 1, 0)) = 1

In case it matters: The SQL is run on a Excel-table via VBA and ADODB.

Answer 1

In the where criteria filter on the list of values you would like to see, group by id and in the having clause filter on those ids which have 3 matching rows.

select id from table1
where value in ('A', 'B', 'C') --you can use a result of another query here
group by id
having count(*)=3

If you can have the same id - value pair more than once, then you need to slightly alter the having clause: having count(distinct value)=3

If you want to make it completely dynamic based on a subquery, then:

select id, min(valcount) as minvalcount from table1
cross join (select count(*) as valcount  from table1 where id=2) as t1
where value in (select value from table1 where id=2) --you can use a result of another query here
group by id
having count(*)=minvalcount