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,

Your Det1Id and Det2Id values don't make sense in either your actual or expected output. Please show your query, your exact input, exact output and exact desired output. — philipxy, Feb 14 '15 at 12:55
If your question is similar to that other question, please specify what part of that question won't work for you situation. — GolezTrol, Feb 14 '15 at 12:59
So if there were 3 MasterID=1 rows in detail 1 and 100 in detail 2 then you would output 100 MasterID=1 rows where the Det2Values were the 100 from detail 2 in the same order downwards and the Det1Values were the 3 from detail 1 in the same order downwards followed by 97 nulls? — philipxy, Feb 14 '15 at 13:06
exactly @philipxy ! 100 details in D1 and 97 nulls + 3 details n D2. mwillemse solution make it happen but one problem as i mentioned in the comment for that answer. — a4ashiq, Feb 15 '15 at 06:32
Please put a description of what you want plus correct data in your question. Comments are ephemeral. — philipxy, Feb 15 '15 at 08:04
I provided an answer for the same problem, which works efficiently even if you have more than two details tables. My solution doesn't use `FULL JOIN`, only `LEFT JOIN`. http://stackoverflow.com/questions/27515577/best-way-to-glue-columns-together/27523439#27523439 — Vladimir Baranov, Feb 15 '15 at 09:35
@vladimir-baranov ,I prefer an answer without `FULL JOIN` if available. I 'll check your solution for sure. Thank you. — a4ashiq, Feb 15 '15 at 10:58

MWillemse · Accepted Answer · 2015-02-15T10:33:20.147

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.

Hi @MWillemse , your trick did it almost. but it still have an issue when there are unbalanced NULL entries in the details 'D1Numbered' and 'D2Numbered'. By the word unbalanced, i meant that if one master item does not have a detail in 'D1', then there should be another item(or same item) that does not have a detail entry in 'D2'. I am trying get rid of this issue somehow. but let me know if you have a better solution — a4ashiq, Feb 15 '15 at 06:50
@a4ashiq You are unclear! Use more words and (*correct*) examples in your question to explain what you mean. Carefully refer to particular parts of things. You are giving us incorrect examples and unclear descriptions. We cannot read your mind. Please also explain in your question the *point* of having this format in terms of what you are reporting. (An [SQLFiddle](http://sqlfiddle.com/#!6/d41d8/23962).) — philipxy, Feb 15 '15 at 08:52
@philipxy , I have corrected my example with new scenario. I gave wrong DetID in result table. I corrected that too. Lets others not get confused. and I solved my problem by modifying MWillemse's answer. — a4ashiq, Feb 15 '15 at 09:22
@MWillemse , I have solved it by making a slight modification to your query. I removed the JOIN of 'master' table from 'Det1' and 'Det2' and placed that JOIN with the result table at the end. That did my job.Thank You — a4ashiq, Feb 15 '15 at 09:30

score 1 · Answer 2 · answered Feb 14 '15 at 13:00

1

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?

answered Feb 14 '15 at 13:00

ChrisV

1,309
9
15

This still has DetNValue values appearing in multiple output rows, but the example does not. – philipxy Feb 14 '15 at 19:15
@ChrisV, It was a close but still contains multiple rows.It makes separate row for each Det1 and Det2 for some items. My DetIDs are not continues for all items. May be thats the issue. all other records(with contiues IDs) comes perfect. – a4ashiq Feb 15 '15 at 07:38

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

2 Answers2