SQL finding records containing elements existing all in another table

Question

I want to extract column A with all elements in C are in B.

T1:
+-----+-----+
|  A  |  B  |
+-----+-----+
|  a  |  1  |
|  a  |  2  |
|  a  |  3  |
|  b  |  1  |
|  b  |  2  |
|  b  |  4  |
|  c  |  2  |
|  c  |  3  |
|  c  |  4  |
+-----+-----+

T2:
+-----+
|  C  |
+-----+
|  2  |
|  4  |
+-----+

For example, b is selected because there are records (b, 2) and (b, 4). However, a is not selected because there is no (a, 4).

Desired output:

+-----+
|  A  |
+-----+
|  b  |
|  c  |
+-----+

What should I write for the condition?

select A
from T1
where ...

This should achieve what you're after [SQL Fiddle](http://sqlfiddle.com/#!18/c539b/4) — d0little, May 15 '21 at 13:33
I voted to reopen this question. The specified "duplicate" only has one table, so it is rather unclear how that applies. — Gordon Linoff, May 15 '21 at 13:35

score 0 · Answer 1 · answered May 15 '21 at 13:36

0

This should achieve what you're after.

SELECT A
FROM (
  SELECT A, COUNT(*) [Count]
  FROM T1
  WHERE B IN (SELECT C FROM T2)
  GROUP BY A) t
WHERE t.[Count] >= (SELECT COUNT(*) FROM T2)

SQL Fiddle

answered May 15 '21 at 13:36

d0little

476
4
8

score 0 · Answer 2 · answered May 15 '21 at 13:36

One method joins the two tables together and then counts the matches:

select t1.a
from t1 join
     t2
     on t1.b = t2.c
group by t1.a
having count(*) = (select count(*) from t2);

Note: If t1 can have duplicate rows, use:

having count(distinct t1.b) = (select count(*) from t2)

SQL finding records containing elements existing all in another table

2 Answers2