tf842
asked on
Can an inner join use a Case statement to determine which cols/tbls will be joined?
I currently use queries 1 and 2 against a couple of denormalized tables to populate a 3rd table. Is it possible, and if so, how can I incorporate a CASE statement in the Inner Join to combine query 1 and 2 into a single query.
Currently, some of the Run and Stat prod information is seperated in C_Table where pricing will eventually be updated using A_Table.
Additional information outside scope of this question: This is a clean-up effort using some assumptions to capture pricing not captured in previously run code. Tables A and C will be further processed once this code is executed. Table normalization is scheduled for a future project.
Query 3 is one of my attempts to combine the queries.
--1) Get State rec cnt for Run_Prod_Cd
INSERT INTO Result_Table
(Type_Cd, State, Prod_Cd, Ext_Prod_Nbr, State_Rec_Cnt)
SELECT
a.Type_Cd,
b.State,
a.Prod_Cd,
b.Ext_Prod_Nbr,
Count(1) AS State_Rec_Cnt
FROM B_Table b
INNER JOIN C_Table c
ON c.Data_Id = b.Data_id
INNER JOIN A_Table a
ON a.Type_Cd = df.Type_Cd
AND a.Prod_Cd = d.Run_Prod_Cd
AND a.Ext_Prod_Nbr = b.Run_Ext_Prod_Nbr
WHERE c.Type_Cd = a.Type_Cd
AND c.Price IS NOT NULL
a.Type_Cd,
b.State,
a.Prod_Cd,
b.Ext_Prod_Nbr
ORDER BY
a.Type_Cd,
a.Prod_Cd,
a.Ext_Prod_Nbr
b.State
--2) Get State rec cnt for Stat_Prod_Cd
INSERT INTO Result_Table
(Type_Cd, State, Prod_Cd, Ext_Prod_Nbr, State_Rec_Cnt)
SELECT
a.Type_Cd,
b.State,
a.Prod_Cd,
b.Ext_Prod_Nbr,
Count(1) AS State_Rec_Cnt
FROM B_Table b
INNER JOIN C_Table c
ON c.Data_Id = b.Data_id
INNER A_Table a
ON a.Type_Cd = c.Type_Cd
AND a.Prod_Cd = b.Stat_Prod_Cd
AND a.Ext_Prod_Nbr = b.Stat_Ext_Prod_Nbr
END
WHERE c.Type_Cd = a.Type_Cd
AND c.Price IS NOT NULL
a.Type_Cd,
b.State,
a.Prod_Cd,
b.Ext_Prod_Nbr
ORDER BY
a.Type_Cd,
a.Prod_Cd,
a.Ext_Prod_Nbr
b.State
--3) Attempt at consolidating the code with case statement
INSERT INTO Result_Table
(Type_Cd, State, Prod_Cd, Ext_Prod_Nbr, State_Rec_Cnt)
SELECT
a.Type_Cd,
b.State,
a.Prod_Cd,
b.Ext_Prod_Nbr,
Count(1) AS State_Rec_Cnt
FROM B_Table b
INNER JOIN C_Table c
ON c.Data_Id = b.Data_id
INNER JOIN CASE
WHEN c.Type_Cd = 1 THEN
A_Table a
ON a.Type_Cd = df.Type_Cd
AND a.Prod_Cd = d.Run_Prod_Cd
AND a.Ext_Prod_Nbr = b.Run_Ext_Prod_Nbr
ELSE
A_Table a
ON a.Type_Cd = c.Type_Cd
AND a.Prod_Cd = b.Stat_Prod_Cd
AND a.Ext_Prod_Nbr = b.Stat_Ext_Prod_Nbr
END
WHERE c.Type_Cd = a.Type_Cd
AND c.Price IS NOT NULL
a.Type_Cd,
b.State,
a.Prod_Cd,
b.Ext_Prod_Nbr
ORDER BY
a.Type_Cd,
a.Prod_Cd,
a.Ext_Prod_Nbr
b.State
All help is appreciated.
Thank you,
Sami
Currently, some of the Run and Stat prod information is seperated in C_Table where pricing will eventually be updated using A_Table.
Additional information outside scope of this question: This is a clean-up effort using some assumptions to capture pricing not captured in previously run code. Tables A and C will be further processed once this code is executed. Table normalization is scheduled for a future project.
Query 3 is one of my attempts to combine the queries.
--1) Get State rec cnt for Run_Prod_Cd
INSERT INTO Result_Table
(Type_Cd, State, Prod_Cd, Ext_Prod_Nbr, State_Rec_Cnt)
SELECT
a.Type_Cd,
b.State,
a.Prod_Cd,
b.Ext_Prod_Nbr,
Count(1) AS State_Rec_Cnt
FROM B_Table b
INNER JOIN C_Table c
ON c.Data_Id = b.Data_id
INNER JOIN A_Table a
ON a.Type_Cd = df.Type_Cd
AND a.Prod_Cd = d.Run_Prod_Cd
AND a.Ext_Prod_Nbr = b.Run_Ext_Prod_Nbr
WHERE c.Type_Cd = a.Type_Cd
AND c.Price IS NOT NULL
a.Type_Cd,
b.State,
a.Prod_Cd,
b.Ext_Prod_Nbr
ORDER BY
a.Type_Cd,
a.Prod_Cd,
a.Ext_Prod_Nbr
b.State
--2) Get State rec cnt for Stat_Prod_Cd
INSERT INTO Result_Table
(Type_Cd, State, Prod_Cd, Ext_Prod_Nbr, State_Rec_Cnt)
SELECT
a.Type_Cd,
b.State,
a.Prod_Cd,
b.Ext_Prod_Nbr,
Count(1) AS State_Rec_Cnt
FROM B_Table b
INNER JOIN C_Table c
ON c.Data_Id = b.Data_id
INNER A_Table a
ON a.Type_Cd = c.Type_Cd
AND a.Prod_Cd = b.Stat_Prod_Cd
AND a.Ext_Prod_Nbr = b.Stat_Ext_Prod_Nbr
END
WHERE c.Type_Cd = a.Type_Cd
AND c.Price IS NOT NULL
a.Type_Cd,
b.State,
a.Prod_Cd,
b.Ext_Prod_Nbr
ORDER BY
a.Type_Cd,
a.Prod_Cd,
a.Ext_Prod_Nbr
b.State
--3) Attempt at consolidating the code with case statement
INSERT INTO Result_Table
(Type_Cd, State, Prod_Cd, Ext_Prod_Nbr, State_Rec_Cnt)
SELECT
a.Type_Cd,
b.State,
a.Prod_Cd,
b.Ext_Prod_Nbr,
Count(1) AS State_Rec_Cnt
FROM B_Table b
INNER JOIN C_Table c
ON c.Data_Id = b.Data_id
INNER JOIN CASE
WHEN c.Type_Cd = 1 THEN
A_Table a
ON a.Type_Cd = df.Type_Cd
AND a.Prod_Cd = d.Run_Prod_Cd
AND a.Ext_Prod_Nbr = b.Run_Ext_Prod_Nbr
ELSE
A_Table a
ON a.Type_Cd = c.Type_Cd
AND a.Prod_Cd = b.Stat_Prod_Cd
AND a.Ext_Prod_Nbr = b.Stat_Ext_Prod_Nbr
END
WHERE c.Type_Cd = a.Type_Cd
AND c.Price IS NOT NULL
a.Type_Cd,
b.State,
a.Prod_Cd,
b.Ext_Prod_Nbr
ORDER BY
a.Type_Cd,
a.Prod_Cd,
a.Ext_Prod_Nbr
b.State
All help is appreciated.
Thank you,
Sami
ASKER
I apologize for the confusion. I masked this to protect real code and missed the translation of this segment.
That should read:
INNER JOIN A_Table a
ON a.Type_Cd = c.Type_Cd
AND a.Prod_Cd = b.Run_Prod_Cd
All references to the real aliases should be changed to the Masked alias for this question as follows.
Real Masked
ff. --> a.
d. --> b.
df. --> c.
Sami
That should read:
INNER JOIN A_Table a
ON a.Type_Cd = c.Type_Cd
AND a.Prod_Cd = b.Run_Prod_Cd
All references to the real aliases should be changed to the Masked alias for this question as follows.
Real Masked
ff. --> a.
d. --> b.
df. --> c.
Sami
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
INNER JOIN A_Table a
ON a.Type_Cd = df.Type_Cd