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