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 Paren...
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.
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') Hi olafhelper - Thanks for your time. I understand that it is very difficult to without looking at the data. It is kind of very messy since the CTEs are from multiple tables and finally these CTEs need to combined together - If we look at this dataset below - it is not a straight pull from a single table - this is the final output of the first dataset
GroupID GroupName IncidentID CustomerImpacted
G123 GlobalSupport INC123 Customer111
G123 GlobalSupport INC123 Customer222
G345 North America INC345 Customer333
G345 North America INC345 Customer444if we look at these rows in which the IncidentID is the KEy column we have multple customers, multiple groups - correct?
what do we need to do next? there is another dataset again this dataset is from the CTE which is from few tables - this includes IncidentID which is like parent and it can have multiple rows
cardinality between these 2 dataset - M: M - why?
the same IncidentID in both sides is repeating for example
dataset 1
4 rows for 1 IncidentID INC123 because it has different groups, different customers,
when i go to dataset 2 INC123 can have 50 rows why? INC123 is the parent for few records like
INC123 is the parent of INCHILD1, INCCHILD2, INCCHILD3, INCCHILD4 etc
what is happening now? cartesian join - 4 * 50 = 200
one option i know is
1).Take out the duplicates in dataset1 - so we get 1 row for INC123
does the bridge table or intermediate table logic works here?