Sql intersect conditional

conditional-statements, intersect, sql

Solution

An empty set `INTERSECT`ed with any other set will always be empty. It's like multiplying by 0. You always get 0.

Conditional `INTERSECT`ing will require either a dynamic query, or a staging table, like this:

Initializations

DECLARE @CAN_USE_TABLE1 BIT
DECLARE @CAN_USE_TABLE2 BIT
DECLARE @CAN_USE_TABLE3 BIT
DECLARE @CAN_USE_TABLE4 BIT

DECLARE @TABLE1 AS TABLE ( ABC INT )
DECLARE @TABLE2 AS TABLE ( ABC INT )
DECLARE @TABLE3 AS TABLE ( ABC INT )
DECLARE @TABLE4 AS TABLE ( ABC INT )
DECLARE @RESULT AS TABLE ( ABC INT ) --Adding this result table
INSERT INTO @TABLE1 VALUES (1)
INSERT INTO @TABLE1 VALUES (2)
INSERT INTO @TABLE1 VALUES (3)
INSERT INTO @TABLE2 VALUES (1)
INSERT INTO @TABLE2 VALUES (2)

SET @CAN_USE_TABLE1 = 1
SET @CAN_USE_TABLE2 = 1
SET @CAN_USE_TABLE3 = 0
SET @CAN_USE_TABLE4 = 0

Processing

INSERT INTO @RESULT
SELECT ABC FROM @TABLE1 WHERE @CAN_USE_TABLE1=1 UNION
SELECT ABC FROM @TABLE2 WHERE @CAN_USE_TABLE2=1 UNION
SELECT ABC FROM @TABLE3 WHERE @CAN_USE_TABLE3=1 UNION
SELECT ABC FROM @TABLE4 WHERE @CAN_USE_TABLE4=1

DELETE r FROM @RESULT r
WHERE NOT EXISTS(SELECT 1 FROM @TABLE1 WHERE ABC=r.ABC)
AND @CAN_USE_TABLE1=1;

DELETE r FROM @RESULT r
WHERE NOT EXISTS(SELECT 1 FROM @TABLE2 WHERE ABC=r.ABC)
AND @CAN_USE_TABLE2=1;

DELETE r FROM @RESULT r
WHERE NOT EXISTS(SELECT 1 FROM @TABLE3 WHERE ABC=r.ABC)
AND @CAN_USE_TABLE3=1;

DELETE r FROM @RESULT r
WHERE NOT EXISTS(SELECT 1 FROM @TABLE4 WHERE ABC=r.ABC)
AND @CAN_USE_TABLE4=1;

SELECT * FROM @RESULT;

Result

1
2

Problem

I want to know if we can do an intersect conditional. theres is somes query, but the result is wrong (always empty). I write what it should result. ``` DECLARE @CAN_USE_TABLE1 BIT DECLARE @CAN_USE_TABLE2 BIT DECLARE @CAN_USE_TABLE3 BIT DECLARE @CAN_USE_TABLE4 BIT DECLARE @TABLE1 AS TABLE ( ABC INT ) -- values will be 1,2,3 DECLARE @TABLE2 AS TABLE ( ABC INT ) -- values will be 1,2 DECLARE @TABLE3 AS TABLE ( ABC INT ) --EMPTY TABLE DECLARE @TABLE4 AS TABLE ( ABC INT ) --EMPTY TABLE INSERT INTO @TABLE1 VALUES (1) INSERT INTO @TABLE1 VALUES (2) INSERT INTO @TABLE1 VALUES (3) INSERT INTO @TABLE2 VALUES (1) INSERT INTO @TABLE2 VALUES (2) SET @CAN_USE_TABLE1 = 1 SET @CAN_USE_TABLE2 = 1 SET @CAN_USE_TABLE3 = 1 SET @CAN_USE_TABLE4 = 0 SELECT ABC FROM @TABLE1 WHERE @CAN_USE_TABLE1 = 1 INTERSECT SELECT ABC FROM @TABLE2 WHERE @CAN_USE_TABLE2 = 1 INTERSECT SELECT ABC FROM @TABLE3 WHERE @CAN_USE_TABLE3 = 1 INTERSECT SELECT ABC FROM @TABLE4 WHERE @CAN_USE_TABLE4 = 1 --RESULT SHOULD BE : -- NO RESULT -- -- BECAUSE, AT THIS STAGE, TABLE1 AND TABLE2 AND TABLE3 SHOULD BE INTERSECTED. AND BECAUSE TABLE3 IS EMPTY, THE RESULT IS EMPTY. SET @CAN_USE_TABLE1 = 1 SET @CAN_USE_TABLE2 = 1 SET @CAN_USE_TABLE3 = 0 SET @CAN_USE_TABLE4 = 0 SELECT ABC FROM @TABLE1 WHERE @CAN_USE_TABLE1 = 1 INTERSECT SELECT ABC FROM @TABLE2 WHERE @CAN_USE_TABLE2 = 1 INTERSECT SELECT ABC FROM @TABLE3 WHERE @CAN_USE_TABLE3 = 1 INTERSECT SELECT ABC FROM @TABLE4 WHERE @CAN_USE_TABLE4 = 1 --RESULT SHOULD BE : -- 1 -- 2 -- -- BECAUSE, AT THIS STAGE, TABLE1 AND TABLE2 SHOULD BE INTERSECTED SET @CAN_USE_TABLE1 = 0 SET @CAN_USE_TABLE2 = 1 SET @CAN_USE_TABLE3 = 0 SET @CAN_USE_TABLE4 = 0 SELECT ABC FROM @TABLE1 WHERE @CAN_USE_TABLE1 = 1 INTERSECT SELECT ABC FROM @TABLE2 WHERE @CAN_USE_TABLE2 = 1 INTERSECT SELECT ABC FROM @TABLE3 WHERE @CAN_USE_TABLE3 = 1 INTERSECT SELECT ABC FROM @TABLE4 WHERE @CAN_USE_TABLE4 = 1 --RESULT SHOULD BE : -- 1 -- 2 -- -- BECAUSE, AT THIS STAGE, ONLY TABLE 2 SHOULD BE USED ```

Original source