SQL query for 3 table combinations using multiple full outer joins
2015-12-21 16:55:41
I have a multiple full outer join query that I am trying get working.
The code is in a SQL Fiddle and basically I'm trying to get 3 tables of data and 1 many-many join table to output the results shown below.
The 3 tables are joined in a many-many fashion like TableA->JoinTable->TableB->JoinTable->TableC->JoinTable->TableA
So far the query is returning 9 rows instead of 7 where rows 4, 8 and 9 should be combined into row 4.
I can't seem to figure out how to collapse rows 4, 8 and 9 into 1 row which would give the desired results.
The code is in a SQL Fiddle and basically I'm trying to get 3 tables of data and 1 many-many join table to output the results shown below.
The 3 tables are joined in a many-many fashion like TableA->JoinTable->TableB->JoinTable->TableC->JoinTable->TableA
So far the query is returning 9 rows instead of 7 where rows 4, 8 and 9 should be combined into row 4.
I can't seem to figure out how to collapse rows 4, 8 and 9 into 1 row which would give the desired results.
TableA (TAID int, Title nvarchar(255)) TableB (TBID int, Title nvarchar(255)) TableC (TCID int, Title nvarchar(255)) -- The ABC table joins the other tables. -- The Type column describes the join source and target -- i.e. 'AB' joins TableA and TableB ABC (Type VARCHAR(10), SrcID INT, TgtID INT) -- Load test data INSERT INTO [dbo].[TableA]([TAID], [Title]) VALUES (11, N'A11'), (12, N'B12'), (13, N'C13'), --(14, N'D14'), --(15, N'E15'), (16, N'F16'); INSERT INTO [dbo].[TableB]([TBID], [Title]) VALUES (21, N'J21'), (22, N'K22'), (23, N'L23'), (24, N'M24'); --(25, N'N25'), --(26, N'O26'); INSERT INTO [dbo].[TableC]([TCID], [Title]) VALUES (31, N'R31'), --(32, N'S32'), (33, N'T33'), --(34, N'U34'), (35, N'V35'), (36, N'W36'); INSERT INTO [dbo].[ABC]([Type], [SrcID], [TgtID]) VALUES ('AB', 11, 21), ('AB', 12, 22), --('AB', 13, 23), ('BC', 21, 31), ('BC', 23, 33), --('BC', 22, 32); ('AC', 16, 36); SELECT [TableA].[TAID], [TableA].[Title], [ab].[Type], [ab].[SrcID], [ab].[TgtID], [TableB].[TBID], [TableB].[Title], [bc].[Type], [bc].[SrcID], [bc].[TgtID], [TableC].[TCID], [TableC].[Title], [ac].[Type], [ac].[SrcID], [ac].[TgtID] FROM [dbo].[TableA] AS [TableA] FULL OUTER JOIN (SELECT [Type], [SrcID], [TgtID] FROM [dbo].[ABC] WHERE [Type] = 'AB' ) AS [ab] ON [TableA].[TAID] = [ab].[SrcID] FULL OUTER JOIN [dbo].[TableB] AS [TableB] ON [ab].[TgtID] = [TableB].[TBID] FULL OUTER JOIN (SELECT [Type], [SrcID], [TgtID] FROM [dbo].[ABC] WHERE [Type] = 'BC' ) AS [bc] ON [TableB].[TBID] = [bc].[SrcID] FULL OUTER JOIN [dbo].[TableC] AS [TableC] ON [bc].[TgtID] = [TableC].[TCID] -- Problem Join - how to get TableA and TableC connected FULL OUTER JOIN (SELECT [Type], [SrcID], [TgtID] FROM [dbo].[ABC] WHERE [Type] = 'AC' ) AS [ac] ON [TableA].[TAID] = [ac].[SrcID] AND [TableC].[TCID] = [ac].[TgtID]; -- Returns this: TAID Title Type SrcID TgtID TBID Title Type SrcID TgtID TCID Title Type SrcID TgtID 11 A11 AB 11 21 21 J21 BC 21 31 31 R31 NULL NULL NULL 12 B12 AB 12 22 22 K22 NULL NULL NULL NULL NULL NULL NULL NULL 13 C13 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 16 F16 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 23 L23 BC 23 33 33 T33 NULL NULL NULL NULL NULL NULL NULL NULL 24 M24 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 35 V35 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 36 W36 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL AC 16 36 -- Ideal Results: TAID Title Type SrcID TgtID TBID Title Type SrcID TgtID TCID Title Type SrcID TgtID 11 A11 AB 11 21 21 J21 BC 21 31 31 R31 NULL NULL NULL 12 B12 AB 12 22 22 K22 NULL NULL NULL NULL NULL NULL NULL NULL 13 C13 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 16 F16 NULL NULL NULL NULL NULL NULL NULL NULL 36 W36 AC 16 36 NULL NULL NULL NULL NULL 23 L23 BC 23 33 33 T33 NULL NULL NULL NULL NULL NULL NULL NULL 24 M24 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 35 V35 NULL NULL NULL
2016-01-01 13:33:34
This produces the correct results:
WITH AB AS ( -- Details of AB joining rows SELECT TA.TAID, TA.Title AS TATitle, AB.[Type], AB.SrcID, AB.TgtID, TB.TBID, TB.Title AS TBTitle FROM dbo.ABC AS AB JOIN dbo.TableA AS TA ON TA.TAID = AB.SrcID JOIN dbo.TableB AS TB ON TB.TBID = AB.TgtID WHERE AB.[Type] = 'AB' ), BC AS ( -- Details of BC joining rows SELECT TB.TBID, TB.Title AS TBTitle, BC.[Type], BC.SrcID, BC.TgtID, TC.TCID, TC.Title AS TCTitle FROM dbo.ABC AS BC JOIN dbo.TableB AS TB ON TB.TBID = BC.SrcID JOIN dbo.TableC AS TC ON TC.TCID = BC.TgtID WHERE BC.[Type] = 'BC' ), AC AS ( -- Details of AC joining rows SELECT TA.TAID, TA.Title AS TATitle, AC.[Type], AC.SrcID, AC.TgtID, TC.TCID, TC.Title AS TCTitle FROM dbo.ABC AS AC JOIN dbo.TableA AS TA ON TA.TAID = AC.SrcID JOIN dbo.TableC AS TC ON TC.TCID = AC.TgtID WHERE AC.[Type] = 'AC' ), A AS ( -- Rows in TableA not referenced in ABC SELECT TA.TAID, TA.Title AS TATitle FROM dbo.TableA AS TA WHERE NOT EXISTS ( SELECT * FROM dbo.ABC AS A WHERE A.[Type] IN ('AB', 'AC') AND A.SrcID = TA.TAID ) ), B AS ( -- Rows in TableB not referenced in ABC SELECT TB.TBID, TB.Title AS TBTitle FROM dbo.TableB AS TB WHERE NOT EXISTS ( SELECT * FROM dbo.ABC AS A WHERE (A.[Type] = 'AB' AND A.TgtID = TB.TBID) OR (A.[Type] = 'BC' AND A.SrcID = TB.TBID) ) ), C AS ( -- Rows in TableC not referenced in ABC SELECT TC.TCID, TC.Title AS TCTitle FROM dbo.TableC AS TC WHERE NOT EXISTS ( SELECT * FROM dbo.ABC AS A WHERE A.[Type] IN ('AC', 'BC') AND A.TgtID = TC.TCID ) ) -- Rows from AB & BC that may or may not join to each other SELECT AB.TAID, AB.TATitle AS Title, AB.[Type], AB.SrcID, AB.TgtID, COALESCE(AB.TBID, BC.TBID) AS TBID, COALESCE(AB.TBTitle, BC.TBTitle) AS Title, BC.[Type], BC.SrcID, BC.TgtID, BC.TCID, BC.TCTitle AS Title, NULL AS [Type], NULL AS SrcID, NULL AS TgtID FROM AB FULL JOIN BC ON BC.TBID = AB.TBID UNION ALL -- Rows from AC SELECT AC.TAID, AC.TATitle AS Title, NULL AS [Type], NULL AS SrcID, NULL AS TgtID, NULL AS TBID, NULL AS Title, NULL AS [Type], NULL AS SrcID, NULL AS TgtID, AC.TCID, AC.TCTitle AS Title, AC.[Type], AC.SrcID, AC.TgtID FROM AC UNION ALL -- Rows from A SELECT A.TAID, A.TATitle AS Title, NULL AS [Type], NULL AS SrcID, NULL AS TgtID, NULL AS TBID, NULL AS Title, NULL AS [Type], NULL AS SrcID, NULL AS TgtID, NULL AS TCID, NULL AS Title, NULL AS [Type], NULL AS SrcID, NULL AS TgtID FROM A UNION ALL -- Rows from B SELECT NULL AS TAID, NULL AS Title, NULL AS [Type], NULL AS SrcID, NULL AS TgtID, B.TBID, B.TBTitle AS Title, NULL AS [Type], NULL AS SrcID, NULL AS TgtID, NULL AS TCID, NULL AS Title, NULL AS [Type], NULL AS SrcID, NULL AS TgtID FROM B UNION ALL -- Rows from C SELECT NULL AS TAID, NULL AS Title, NULL AS [Type], NULL AS SrcID, NULL AS TgtID, NULL AS TBID, NULL AS Title, NULL AS [Type], NULL AS SrcID, NULL AS TgtID, C.TCID AS TCID, C.TCTitle AS Title, NULL AS [Type], NULL AS SrcID, NULL AS TgtID FROM C;
Despite the length of the query, the logic is quite simple, and so is the execution plan:
2016-01-04 19:49:24
No worries. It was an interesting problem to think about.
Thank you very much.