如何将主表与多明细表连接起来，其中多行明细单元格填充为空

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.

我不确定题目是否合适。我的问题类似于线程如何将多个细节表连接到Header表。但是这个也提供重复的记录。这是我的情况，我有一个主表和两个细节表。

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,

谢谢你！

2 个解决方案

#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.

编辑:确实有一个小错误，我更新了上面的查询。修正方法是用D替换分区。主id除以分区M。ID，现在RowNr为每个主记录从1开始，这在以前的修订中没有。

#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?

如果值匹配，这将只显示两个细节表，这似乎是您想要的?

#1