2

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

  • 2
    The problem is called "relational division". – ypercubeᵀᴹ May 4 '18 at 20:08
1
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)
              )
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.