TSQL – Conditionally “Cross Join” Records

Code (staging sample data):

DECLARE @LookupTab TABLE
    (
        [Val] INT NOT NULL
    ) ;
INSERT INTO @LookupTab
VALUES
    ( 111 ) -- Option 1
  , ( 112 ) -- Option 2
;

DECLARE @DataTab TABLE
    (
        [Id]    INT IDENTITY(1, 1) NOT NULL
      , [Val]   INT NOT NULL
      , [Extra] VARCHAR(10)
    ) ;
INSERT INTO @DataTab
VALUES
    ( 111, 'One' )  -- Option 1
  , ( 111, 'One' )  -- Option 1
  , ( 112, 'Two' )  -- Option 2
  , ( 223, 'Both' ) -- Option 1 & 2
  , ( 223, 'Both' ) -- Option 1 & 2
;

Detail: @LookupTab is basically a filter that will have either 0, 1, or both values (111 – Option 1 and 112 – Option 2). @DataTab is the actual data in the table (can be huge). The Val field in this table can either be 111 (Option 1), 112 (Option 2), or 223 (Both Options).

Expectations:

If @LookupTab only passes in 111, I want Id’s 1,2,4,5 returned (total 4 rows). Like so

Id  Val Extra
1   111 One
2   111 One
4   111 Both
5   111 Both

If @LookupTab only passes in 112, I want Id’s 3,4,5 returned (total 3 rows)

Id  Val Extra
3   112 Two
4   112 Both
5   112 Both

If @LookupTab passes in 111 and 112, I want Id’s 1,2,3,4,5 returned, but since Id 4 and 5 are “Both Options”, I want two rows returned for each. Like so

Id  Val Extra
1   111 One
2   111 One
3   112 Two
4   111 Both
4   112 Both
5   111 Both
5   112 Both

My unsuccessful try:

SELECT      [T1].[Id]
          , [T2].[Val]
          , [T1].[Extra]
FROM        @DataTab AS [T1]
CROSS JOIN  @LookupTab AS [T2]
--WHERE       [T1].[Val] = [T2].[Val] ;

Answer

What you need is a INNER JOIN like this:

SELECT      [T1].[Id]
          , [T2].[Val]
          , [T1].[Extra]
FROM        @DataTab AS [T1]
INER JOIN   @LookupTab AS [T2]
ON          [T2].[Val] = [T1].[Val] OR [T1].[Val] = 223
ORDER BY    [T1].[Id]

See the demo.

Advertisement

Answer