CODEWITHSUNDEEP
sql-serveroptimization
user17611369
user17611369

Reputation:

combining multiple queries / query optimization

I have made a little example data that I modify in three steps. I cant do it in one, maybe there is a clever way with some logic? I use Microsoft SQL Server

This code will generate the four base tables with example data and the step by step queries I want to combine, the result at the end should have 8 entries:

Reference table:

CREATE TABLE ref 
(
    ID int NOT NULL
    NR int NOT NULL
    CONSTRAINT KEYS PRIMARY KEY (ID, NR)
);

INSERT INTO ref
VALUES (1234, 223), (1234, 224), (1234, 225),
       (1235, 123), (1235, 124), (1236, 540),
       (1236, 541), (1237, 233), (1237, 234);

Con1 table:

CREATE TABLE con1 
(
    NR int NOT NULL
    flag int NOT NULL
    PRIMARY KEY (NR)
);

INSERT INTO con1
VALUES (123, 0), (124, 1), (125, 0),
       (220, 0), (222, 0), (223, 0), 
       (224, 0), (225, 1), (300, 0),
       (540, 1), (541, 1);

Con2 table:

CREATE TABLE con2 
(
    NR int NOT NULL
    ID int NOT NULL
    PRIMARY KEY (NR)
);

INSERT INTO con2
VALUES (123, 1235), (124, 1235), (125, 1243),
       (220, 1296), (222, 1255), (223, 1234),
       (224, 1234), (225, 1234), (300, 1267),
       (540, 1236);

Info table:

CREATE TABLE info 
(
    NR int NOT NULL
    SNR int NOT NULL
    SSNR int NOT NULL
    Level int not NULL
    CONSTRAINT KEYS PRIMARY KEY (NR, SNR, SSNR)
);

INSERT INTO info
VALUES (123, 1, 1, 1), (123, 1, 2, 2),
       (123, 1, 3, 2), (123, 2, 1, 1),
       (123, 2, 2, 2), (123, 2, 3, 2),
       (124, 1, 1, 1), (124, 1, 2, 2),
       (124, 1, 3, 2), (125, 1, 1, 1),
       (125, 1, 2, 2), (125, 1, 3, 2),
       (125, 1, 4, 3), (125, 1, 5, 3),
       (220, 1, 1, 1), (220, 1, 2, 2),
       (223, 1, 1, 1), (223, 1, 2, 2),
       (224, 1, 1, 1), (224, 1, 2, 2),
       (224, 1, 3, 2), (225, 1, 1, 1),
       (225, 1, 2, 2), (300, 1, 1, 1),
       (300, 1, 2, 2), (300, 2, 1, 1),
       (300, 2, 2, 2), (540, 1, 1, 1),
       (541, 1, 1, 1);

Step #1:

SELECT * 
FROM con1
INNER JOIN con2 ON con1.NR = con2.NR
WHERE con1.flag = 1

Step #2:

SELECT ref.* 
FROM ref
INNER JOIN step1 ON ref.ID = step1.ID

Step #3:

SELECT * 
FROM step2
INNER JOIN info ON step2.NR = info.NR
WHERE info.Level = 1

I tried some different ways but always get too much resulting rows

the result should look like this:

ID NR Level SNR SSNR
1234 223 1 1 1
1234 224 1 1 1
1234 225 1 1 1
1235 123 1 1 1
1235 123 1 2 1
1235 124 1 1 1
1236 540 1 1 1
1236 541 1 1 1

It should be all entries from info with Level=1 excluding:

but including:

the result has the same columns as info with on NR matching IDs from ref

Upvotes: 0

Views: 90

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415840

You can do this easily with Common Table Expressions:

with step1 As (
    Select * 
    From con1
    Inner Join con2 On con1.NR = con2.NR
    Where con1.flag = 1
), step2 As (
    Select ref.* 
    From ref
    Inner Join step1 On ref.ID = step1.ID
)
Select * 
From step2
Inner Join info On step2.NR = info.NR
Where info.Level = 1

Upvotes: 1

Related Questions