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

optimizationperformancequery-performancerelational-divisionsql servert-sql

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

Example

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?)

Best Answer

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)
              )
Related Question