Retrieving the conditional intersection of the tables

First we create three tables and fill them with sample data.

CREATE TABLE [dbo].[DataSet1](
	[Id] [INT] IDENTITY(1,1) NOT NULL,
	[Col1] [CHAR](1) NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[DataSet2](
	[Id] [INT] IDENTITY(1,1) NOT NULL,
	[Col1] [CHAR](1) NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[DataSet3](
	[Id] [INT] IDENTITY(1,1) NOT NULL,
	[Col1] [CHAR](1) NOT NULL
) ON [PRIMARY]

GO

INSERT INTO dbo.DataSet1(Col1)
SELECT 'A'
UNION ALL
SELECT 'Z'
UNION ALL
SELECT 'Y'

INSERT INTO dbo.DataSet2(Col1)
SELECT 'Z'
UNION ALL
SELECT 'X'
UNION ALL
SELECT 'Y'

INSERT INTO dbo.DataSet3(Col1)
SELECT 'A'
UNION ALL
SELECT 'Z'
UNION ALL
SELECT 'X'
UNION ALL
SELECT 'B'

We have to retrieve the intersection of these tables based on conditions.

DECLARE @takeDataSet1Dimension BIT = 1;
DECLARE @takeDateSet2Dimension BIT = 0;
DECLARE @takeDateSet3Dimension BIT = 1;

DECLARE @dimensionCounter INT = 0;

IF (@takeDataSet1Dimension = 1)
    SET @dimensionCounter = @dimensionCounter + 1;

IF (@takeDateSet2Dimension = 1)
    SET @dimensionCounter = @dimensionCounter + 1;
    
IF (@takeDateSet3Dimension = 1)
    SET @dimensionCounter = @dimensionCounter + 1;
    
WITH d AS (
SELECT Col1
FROM dbo.DataSet1
WHERE @takeDataSet1Dimension = 1
UNION ALL
SELECT Col1
FROM dbo.DataSet2
WHERE @takeDateSet2Dimension = 1
UNION ALL
SELECT Col1
FROM dbo.DataSet3
WHERE @takeDateSet3Dimension = 1
), dd AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) AS dimensionAmount, Col1
   FROM d
)
select Col1, dimensionAmount
from dd
where dimensionAmount = @dimensionCounter

In this example, we take the intersection of the first and third table. We will get two rows: A and Z

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s