# Query For Where Table 1 (A,B,C) for each distinct A, has rows matching all rows in Table 2 (B,C)

I'm trying to optimize a query here:

Given Table 1 (A,B,C) and Table 2 (B,C)

Given Table 1 contains many unique tuples (B,C) for each A

Select (1.A, 1.B, 1.C)

Where for a given set of rows where 1.A is distinct, the set of rows must collectively satisfy all of the rows in 2 where 1.B = 2.B and 1.C = 2.C

## TABLE 1

``````| A | B  | C  |
|---|----|----|
| 1 | B1 | C1 |
| 1 | B2 | C2 |
| 2 | B1 | C1 |
| 2 | B2 | C2 |
| 2 | B3 | C3 |
| 3 | B1 | C1 |
``````

## TABLE 2

``````| B  | C  |
|----|----|
| B1 | C1 |
| B2 | C2 |
``````

## RESULT

``````| A | B  | C  |
|---|----|----|
| 1 | B1 | C1 |
| 1 | B2 | C2 |
| 2 | B1 | C1 |
| 2 | B2 | C2 |
| 2 | B3 | C3 |
``````

(Only 3 was excluded because it failed to match both rows of table 2)

Current implementation is dynamic SQL

``````select Result.A
from
(
SELECT vtab1.A
FROM table1 vtab1
where vtab1.[B]= 'B1' and vtab1.[C] = 'C1'
union all
SELECT b.VaultObjectId
FROM table1 vtab2
where vtab2.[B]= 'B2' and vtab2.[C] = 'C2'
) AS Result
Group By A
HAVING
COUNT(A) = 2
``````

However this is all generated dynamically, and doesn't remerge my A with the matching B (which I suppose is easy enough)

Related: Is there any way to "name" a query to be used later (not computed, but deferred?)

• The problem is called "relational division". – ypercubeᵀᴹ May 4 '18 at 20:08

``````select *
from t1
where t1.a in ( select t1.a
from t1
join t2
on t2.b = t1.b
and t2.c = t2.c
group by t1.a
having count(*) = (select count(*) from t2)
)
``````

I find that the easiest way to deal with relational division challenges is to phrase the question in the negative form: "Show me all rows from T1, for which there doesn't exist a row in T2, for which there doesn't exist another row from T1, with the same A value, and matching B and C values". It seems weird at first, but once you get used to it, the translation to SQL is immediate, elegant, and typically the most efficient:

``````SELECT  *
FROM    T1
WHERE   NOT EXISTS  (
SELECT  NULL
FROM    T2
WHERE   NOT EXISTS  (
SELECT  NULL
FROM    T1 AS T1A
WHERE   T1A.B = T2.B
AND
T1A.C = T2.C
AND
T1A.A = T1.A
)
);
``````