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
SQL 2019 Enterprise Cluster setup
Hey Guys, I'm in the process of setting up Always on HA cluster on SQL 2019 Enterprise on virtual environment. Cluster will include SQL server instance on two separate Virtual machines with High availability groups - Synchronous replication - Secondary - "Readable Secondary -NO" ? Please help me clarify if; a. Microsoft licensing allows having two SQL instances on two separate VM's with one license? b. if yes, should the other node be Read-Only & Synchronous replication? c. If yes, how will it help incase of DR or if downtime is required on Primary node? d. If (b) is no, and both nodes have writable copy how does license taken into count? thanks