pulled datasets from CTEs but got stuck with joining them together

Question

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 exampleWith CTE Parent Incident AS(),Test1 AS&nbsp;(),Test2 AS(),Final_Parent_Data AS(data from the 3 CTEs above joined)SELECT * FROM Final_Parent_Datasay 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&nbsp;GroupID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GroupName&nbsp; &nbsp; &nbsp; &nbsp;IncidentID&nbsp; &nbsp; &nbsp; &nbsp;CustomerImpacted&nbsp;G123&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GlobalSupport&nbsp; &nbsp; INC123&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Customer111G123&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; GlobalSupport&nbsp; &nbsp; INC123&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Customer222G345&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; North America&nbsp; &nbsp;INC345&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Customer333G345&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;North America&nbsp; &nbsp; INC345&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Customer444&nbsp;This is my parent kind of dataset now i have to get the child dataset&nbsp; which is based on some CTES for ex; how is the child dataset linked to the parent dataset based on&nbsp;IncidentIDCTE Child_Dataset AS(),Test888 AS(),Test999 AS(),Final_Child_Dataset AS(CTEs joined)select * from&nbsp;Final_Child_DatasetIncidentID&nbsp; &nbsp; &nbsp; Incdent_Task_ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Incident Task NameINC123&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ITASK1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TaskName1INC123&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ITASK2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TaskName2INC123&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ITASK3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TaskName3INC123&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ITASK4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TaskName4INC123&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ITASK5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TaskName5INC345&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ITASK888&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TaskName888INC345&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ITASK999&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TaskName999INC345&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ITASK111&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TaskName111&nbsp;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 understandI have to use the&nbsp; &nbsp; &nbsp; &nbsp; IncidentID to join - but as we can see this will cause&nbsp; the cartesian join&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4 * 8 = 32 records&nbsp;what are the best options to get the cartesian join removed -&nbsp;&nbsp; &nbsp; &nbsp;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 joinSelectA.*mB.*FROM Final_Parent_Data&nbsp;A FULL OUTER JOIN&nbsp;Final_Child_Dataset B ON A.IncidentID=B.IncidentID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

rpreddiz123 · Answer

any suggestions please..just looking for the ideas

olafhelper · Answer

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.

rpreddiz123 · Answer

olafhelper&nbsp;&nbsp;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 datasetGroupID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GroupName&nbsp; &nbsp; &nbsp; &nbsp;IncidentID&nbsp; CustomerImpactedG123&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GlobalSupport&nbsp; &nbsp; &nbsp;INC123&nbsp; &nbsp; &nbsp; Customer111G123&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GlobalSupport&nbsp; &nbsp; &nbsp;INC123&nbsp; &nbsp; &nbsp; Customer222G345&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;North America&nbsp; &nbsp; &nbsp;INC345&nbsp; &nbsp; &nbsp; Customer333G345&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; North America&nbsp; &nbsp; &nbsp;INC345&nbsp; &nbsp; &nbsp; Customer444&nbsp;if 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&nbsp;includes IncidentID which is like parent and it can have multiple rowscardinality between these 2 dataset - M: M - why?the same IncidentID in both sides is repeating for exampledataset 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4 rows&nbsp; for 1 IncidentID&nbsp; &nbsp; &nbsp; &nbsp;INC123&nbsp; &nbsp; 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 likeINC123 is the parent of INCHILD1, INCCHILD2, INCCHILD3, INCCHILD4 etcwhat is happening now? cartesian join - 4 * 50 = 200one option i know is1).Take out the duplicates in dataset1 - so we get 1 row for INC123&nbsp;does the bridge table or intermediate table logic works here?&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

rpreddiz123 · Answer

Hi olafhelper, please see if this helpsCREATE 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')

Forum Discussion

pulled datasets from CTEs but got stuck with joining them together

Share

Resources