How to Join Multiple Detail Tables to Header Table

Question

I need to combine one Header Table and six Detail Tables into a single result. To demonstrate I have created a very simple example as follows:

DECLARE @MyHeader TABLE (HeaderPK int, Name varchar(100), Total smallmoney)
INSERT INTO @MyHeader (HeaderPK, Name, Total) 
    SELECT 1, 'ABC Company', 1600

DECLARE @MyDetail1 TABLE (Detail1PK int, HeaderFK int, Detail1Description varchar(100), Detail1Amount smallmoney)
INSERT INTO @MyDetail1 (Detail1PK, HeaderFK, Detail1Description, Detail1Amount) 
    SELECT 1, 1, 'Detail 1A', 100
    UNION SELECT 2, 1, 'Detail 1B', 300

DECLARE @MyDetail2 TABLE (Detail2PK int, HeaderFK int, Detail2Description varchar(100), Detail2AmountA smallmoney, Detail2AmountB smallmoney)
INSERT INTO @MyDetail2 (Detail2PK, HeaderFK, Detail2Description, Detail2AmountA, Detail2AmountB) 
    SELECT 1, 1, 'Detail 2A', 100, 100
    UNION SELECT 2, 1, 'Detail 2B', 200, 200
    UNION SELECT 3, 1, 'Detail 3C', 300, 300

-- Returns 2 Rows, Expected 2
SELECT 
    MyHeader.*
    ,MyDetail1.*
FROM
    @MyHeader MyHeader
    FULL JOIN @MyDetail1 MyDetail1 ON MyHeader.HeaderPK = MyDetail1.HeaderFK
ORDER BY
    MyDetail1.Detail1PK

-- Returns 6 Rows, Expected 3
SELECT 
    MyHeader.*
    ,MyDetail1.*
    ,MyDetail2.*
FROM
    @MyHeader MyHeader
    FULL JOIN @MyDetail1 MyDetail1 ON MyHeader.HeaderPK = MyDetail1.HeaderFK
    FULL JOIN @MyDetail2 MyDetail2 ON MyHeader.HeaderPK = MyDetail2.HeaderFK

Notes:

• MSSQL 2008R2
• Each Detail Table will have approximately 0 to 15 records.

In the sample, the Detail Tables have similar structures. In the production system, they are very different.

Answer 1

When you match @MyDetail1 with @MyHeader you get two rows. If you match @MyDetail2 with @MyHeader you will get three rows. However, in your last join you do not tell the system how to match the two rows in @MyDetail1 with the three rows in @MyDetail2 and thus the system assumes you want to match every row of @MyDetail1 with every row of @MyDetail2 resulting 2 x 3 rows or 6 rows.

Solutions:

1. If @MyDetail1 and @MyDetail2 are supposed to be directly related to each other, then there is a Join missing.
2. If you only want @MyDetail1 and then @MyDetail2, then use two Select clauses.

Answer 2

Each join is performed for each row. So if you join a table with 1 row on a table with matching 2 rows, you get 2 rows. If you join that on a table with 3 rows, you get 6 rows, and so on.

One solution is to execute multiple SQL statements, one for each detail table. The client can move to the next result with the equivalent of SqlDataReader.NextResult().

Another option is to union all the different statements together; this will result in too many columns, but the rowcount will be reduced.

Answer 3

This will give you what you want, based on a slot upwards retrieval of the detail tables. Frankly I am showing this code only because it is mildly interesting. There is normally no good reason to do this type of query.

For transfer efficiency, this would probably be better not to repeat the data in the header columns:

Header
Detail1
Detail2
Detail3
etc

SQL Query

DECLARE @MyHeader TABLE (HeaderPK int, Name varchar(100), Total smallmoney)
INSERT INTO @MyHeader (HeaderPK, Name, Total) 
    SELECT 1, 'ABC Company', 1600

DECLARE @MyDetail1 TABLE (Detail1PK int, HeaderFK int, Detail1Description varchar(100), Detail1Amount smallmoney)
INSERT INTO @MyDetail1 (Detail1PK, HeaderFK, Detail1Description, Detail1Amount) 
    SELECT 1, 1, 'Detail 1A', 100
    UNION SELECT 2, 1, 'Detail 1B', 300

DECLARE @MyDetail2 TABLE (Detail2PK int, HeaderFK int, Detail2Description varchar(100), Detail2AmountA smallmoney, Detail2AmountB smallmoney)
INSERT INTO @MyDetail2 (Detail2PK, HeaderFK, Detail2Description, Detail2AmountA, Detail2AmountB) 
    SELECT 1, 1, 'Detail 2A', 100, 100
    UNION SELECT 2, 1, 'Detail 2B', 200, 200
    UNION SELECT 3, 1, 'Detail 3C', 300, 300

DECLARE @MyDetail3 TABLE (Detail3PK int, HeaderFK int, Detail3Description sysname)
INSERT INTO @MyDetail3 (Detail3PK, HeaderFK, Detail3Description) 
    SELECT 1, 1, 'Detail 3A'
    UNION SELECT 2, 1, 'Detail 3B'

-- Returns 6 Rows, Expected 3
SELECT 
    MyHeader.*
    ,MyDetail1.*
    ,MyDetail2.*
    ,MyDetail3.*
FROM
    @MyHeader MyHeader
    LEFT JOIN
    (SELECT *, RN=ROW_NUMBER() over (order by Detail1PK) FROM @MyDetail1) MyDetail1
    FULL JOIN
    (SELECT *, RN=ROW_NUMBER() over (order by Detail2PK) FROM @MyDetail2) MyDetail2
    ON MyDetail1.HeaderFK = MyDetail2.HeaderFK AND MyDetail1.RN = MyDetail2.RN
    FULL JOIN
    (SELECT *, RN=ROW_NUMBER() over (order by Detail3PK) FROM @MyDetail3) MyDetail3
    ON COALESCE(MyDetail1.HeaderFK, MyDetail2.HeaderFK) = MyDetail3.HeaderFK
       AND COALESCE(MyDetail1.RN, MyDetail2.RN) = MyDetail3.RN
    ON MyHeader.HeaderPK = COALESCE(MyDetail1.HeaderFK, MyDetail2.HeaderFK)

You expand into more detail tables by adding more to the COALSECE, so the 6th detail table would be

    FULL JOIN
    (SELECT *, RN=ROW_NUMBER() over (order by Detail6PK) FROM @MyDetail6) MyDetail6
    ON COALESCE(MyDetail1.HeaderFK, MyDetail2.HeaderFK, MyDetail3.HeaderFK,
        MyDetail4.HeaderFK, MyDetail5.HeaderFK) = MyDetail6.HeaderFK
       AND COALESCE(MyDetail1.RN, MyDetail2.RN, MyDetail3.RN, MyDetail4.RN, MyDetail5.RN) = MyDetail6.RN