How to join master table to multiple details table with extra rows of details cell filled with NULL

Question

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,

Answer 1

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.

Answer 2

I'm not super sure but I think what you want is this:

SELECT m.MasterID, m.Name, d1.DetailsID Det1ID, d1.Value Det1Value, d2.DetailsID Det2ID, d2.Value Det2Value
FROM Details1 d1
    FULL OUTER JOIN Details2 d2 ON d1.FKMasterID = d2.FKMasterID AND d1.Value = d2.Value
    RIGHT JOIN Master m ON d1.FKMasterID = m.MasterID OR d2.FKMasterID = m.MasterID

This will only show both Details tables if the Values match, which seems to be what you want?