Forum Discussion
pulled datasets from CTEs but got stuck with joining them together
Hi - Thanks for your time, I would like to give the scenario clearly and kindly request for some ideas on the best route..
I am using CTEs to pull data ... say for example data that belongs to Parent Incident and this parent incident dataset KEY column has duplicates because some other columns in that dataset has more than 1 value for example
With CTE Parent Incident AS
(
),
Test1 AS
(
),
Test2 AS
(
),
Final_Parent_Data AS
(
data from the 3 CTEs above joined
)
SELECT * FROM Final_Parent_Data
say from the 3 CTEs above I have the parent incident dataset that i need which also has some other columns that are causing the KEY column values to be repeating
GroupID GroupName IncidentID CustomerImpacted
G123 GlobalSupport INC123 Customer111
G123 GlobalSupport INC123 Customer222
G345 North America INC345 Customer333
G345 North America INC345 Customer444
This is my parent kind of dataset now i have to get the child dataset which is based on some CTES for ex; how is the child dataset linked to the parent dataset based on IncidentID
CTE Child_Dataset AS
(
),
Test888 AS
(
),
Test999 AS
(
),
Final_Child_Dataset AS
(
CTEs joined
)
select * from Final_Child_Dataset
IncidentID Incdent_Task_ID Incident Task Name
INC123 ITASK1 TaskName1
INC123 ITASK2 TaskName2
INC123 ITASK3 TaskName3
INC123 ITASK4 TaskName4
INC123 ITASK5 TaskName5
INC345 ITASK888 TaskName888
INC345 ITASK999 TaskName999
INC345 ITASK111 TaskName111
Now I have to do Full Outer JOIN to get all the records from both sides (Why? to get anything that is not mapped) - I just gave an example simple to understand
I have to use the IncidentID to join - but as we can see this will cause the cartesian join
4 * 8 = 32 records
what are the best options to get the cartesian join removed -
Note: This is just an example of what i have been working on - my dataset is in this format, and iam using the CTE's but the final dataset that comes out of the CTE is exactly like above - the KEY field in both places is M:M - thanks for your time, what could be best options to remove the cartesian join that is caused by the join
Select
A.*m
B.*
FROM Final_Parent_Data A FULL OUTER JOIN Final_Child_Dataset B ON A.IncidentID=B.IncidentID
4 Replies
- any suggestions please..just looking for the ideas
- We don't have your database to test anything.
lease post table design as DDL, some sample data as DML statement and the expected result.- Hi olafhelper, please see if this helps
CREATE TABLE Incident_Parent (
GroupID varchar(10),
GroupName varchar(25),
IncidentID varchar(10),
CustomerImpacted varchar(50)
);
INSERT INTO Incident_Parent values ('G123', 'GlobalSupport', 'INC123', 'Customer111' )
INSERT INTO Incident_Parent VALUES ('G123','GlobalSupport','INC123','Customer222')
INSERT INTO Incident_Parent VALUES ('G345','North America','INC345','Customer333')
INSERT INTO Incident_Parent VALUES ('G345','North America','INC345','Customer444')
CREATE TABLE Incident_Child (
IncidentID varchar(10),
Incident_Task_ID varchar(15),
Incident_Task_Name varchar(25)
);
INSERT INTO Incident_Child values ('INC123','ITASK1','TaskName1')
INSERT INTO Incident_Child values('INC123','ITASK2','TaskName2')
INSERT INTO Incident_Child values('INC123','ITASK3','TaskName3')
INSERT INTO Incident_Child values('INC123','ITASK4','TaskName4')
INSERT INTO Incident_Child values('INC123','ITASK5','TaskName5')
INSERT INTO Incident_Child values('INC345','ITASK888','TaskName888')
INSERT INTO Incident_Child values('INC345','ITASK999','TaskName999')
INSERT INTO Incident_Child values('INC345','ITASK111','TaskName111')
