1

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
runamuk0
  • 784
  • 1
  • 7
  • 20
  • Could you explain a logic that must be used to join (combine) values from these tables ? How the table in your question (with header ABC) relates to data shown in the Fiddle ? I can't see any `A,B,C` columns, nor any `1,2,3` values in these tables. For now we have Fiddle, a wrong query that gives wrong results, and a table in the question that doesn't corresponds to data in Fiddle. The question is unclear. – krokodilko Dec 19 '15 at 19:27
  • @kordirko Added tables, data, query and results. – runamuk0 Dec 20 '15 at 12:55

0 Answers0