I am not sure how appropriate is the question title. My problem is similar to the thread How to Join Multiple Detail Tables to Header Table . But this one too giving duplicate records.
Here is my situation
I have a master table and two details tables.
MasterID | Name
----------------------- // Master table
1 Item1
2 Item2
3 Item3
4 Item4
5 Item5
-----------------------
Det1ID | FKMasterID | Value
-----------------------------
1 1 Det1-Val1
2 1 Det1-Val2
3 2 Det1-Val3
Det2ID | FKMasterID | Value
-----------------------------
1 1 Det2-Val1
2 1 Det2-Val2
3 1 Det2-Val3
4 3 Det2-Val4
5 5 Det2-Val5
----------------------------------
The Tables are somewhat like this.
When I make required left-right joins , I get result in this way.
MasterID | Name | Det1ID | Det1Value | Det2ID | Det2Value
------------------------------------------------------------
1 Item1 1 Det1-Val1 1 Det2-Val1
1 Item1 1 Det1-Val1 2 Det2-Val2
1 Item1 1 Det1-Val1 3 Det2-Val3
1 Item1 2 Det1-Val2 1 Det2-Val1
1 Item1 2 Det1-Val2 2 Det2-Val2
1 Item1 2 Det1-Val2 3 Det2-Val3
2 Item2 3 Det1-Val3 NULL NULL
3 Item3 NULL NULL 4 Det2-Val4
4 Item4 NULL NULL NULL NULL
5 Item5 NULL NULL 5 Det2-Val5
-------------------------------------------------------------
What I expect to get is
MasterID | Name | Det1ID | Det1Value | Det2ID | Det2Value
------------------------------------------------------------
1 Item1 1 Det1-Val1 1 Det2-Val1
1 Item1 2 Det1-Val2 2 Det2-Val2
1 Item1 NULL NULL 3 Det2-Val3
2 Item2 3 Det1-Val3 NULL NULL
3 Item3 NULL NULL 4 Det2-Val4
4 Item4 NULL NULL NULL NULL
5 Item5 NULL NULL 5 Det2-Val5
------------------------------------------------------------
I don't want the details value to be duplicated for any of the master item.
Is there any way to do this?? only iterate with a cursor is the way?? A little help is appreciated.
Thank you,
It proved to be a bit more tricky than I initially thought, but the following should do the trick. The code should be pretty self explanatory.
WITH [master] AS(
SELECT * FROM (VALUES
(1, 'Item1')
,(2, 'Item2')
,(3, 'Item3')
,(4, 'Item4')
,(5, 'Item5')
) AS T(ID, Value)
),
Det1 AS (
SELECT * FROM (VALUES
(1, 1, 'Det1-Val1')
,(2, 1, 'Det1-Val2')
,(3, 2, 'Det1-Val3')
) AS T(ID, MasterID, Value)
),
Det2 AS (
SELECT * FROM (VALUES
(1, 1, 'Det2-Val1')
,(2, 1, 'Det2-Val2')
,(3, 1, 'Det2-Val3')
,(4, 3, 'Det2-Val4')
,(5, 5, 'Det2-Val5')
) AS T(ID, MasterID, Value)
),
Det1Numbered AS(
SELECT MasterID = M.ID ,
MasterValue = M.Value ,
Det1ID = D.ID ,
Det1Value = D.Value,
RowNr = ROW_NUMBER() OVER (PARTITION BY M.ID ORDER BY D.ID)
FROM [master] AS M
LEFT JOIN Det1 AS D
ON M.ID = D.MasterID
),
Det2Numbered AS(
SELECT MasterID = M.ID ,
MasterValue = M.Value ,
Det2ID = D.ID ,
Det2Value = D.Value,
RowNr = ROW_NUMBER() OVER (PARTITION BY M.ID ORDER BY D.ID)
FROM [master] AS M
LEFT JOIN Det2 AS D
ON M.ID = D.MasterID
)
SELECT MasterID = COALESCE(D1.MasterID, D2.MasterID),
MasterValue = COALESCE(D1.MasterValue, D2.MasterValue),
D1.Det1ID ,
D1.Det1Value ,
D2.Det2ID ,
D2.Det2Value
FROM Det1Numbered AS D1
FULL JOIN Det2Numbered AS D2
ON D1.MasterID = D2.MasterID
AND D2.RowNr = D1.RowNr
ORDER BY MasterID
Edit: There indeed was a little bug in there, I've updated the query above. The fix is to replace PARTITION BY D.MasterID by PARTITION BY M.ID, now RowNr starts at 1 for each master record which it did not in the previous revision.