SQL query for 3 table combinations using multiple full outer joins

runamuk0@msn.com 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.

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
SQLkiwi 2016-01-01 13:33:34
This produces the correct results:

SQLFiddle

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:

Execution plan

Tony Green 2016-01-04 17:26:13
Awesome solution! I would never have thought to tackle it in this manner.
Thank you very much.
SQLkiwi 2016-01-04 19:49:24
No worries. It was an interesting problem to think about.
December 21, 2015
No Comments