MYSQL checking all values in a set match another s

问题:

I have two tables A and B with values below

Table A

   X
------
1
2
3

Table B

X       Y
------  ------
1       A
2       A
3       A
1       B
2       B
1       C
3       D

I need to find only the Y values from Table B which match All of the values in Table A. So for the above example the only Y value that matches is A (A has an X value of 1,2,and 3)

回答1:

This is an example of a "set-within-sets" subquery. I like to approach this with aggregation and a having clause.

select b.y
from tableB b join
     tableA a
     on b.X = a.X
group by b.y
having count(distinct b.x) = (select count(*) from tableA);