Joining multiple child tables to one parent table WITHOUT getting all possible combinations

发布于 2020-11-28 12:28:16

Assuming we have multiple child tables child1, child2, child3... with 1-to-n relationship to one parent table parent.

----------     ----------     ----------     ----------
| parent |     | child1 |     | child2 |     | child3 |
|--------|     |--------|     |--------|     |--------|
|parentID|     |child1ID|     |child2ID|     |child3ID|
----------     |parentID|     |parentID|     |parentID|
               ----------     ----------     ----------

What is the best way to get all related data to one record of parentID?

I tried LEFT JOIN but that resulted in a lot of unnecessary rows (all combinations, numRows parent x numRows child1 x numRows child2 x numRows child3). So I got very fast thousands of rows for just a bunch of real data rows:

SELECT
child1.child1ID,
child2.child2ID,
child3.child3ID
FROM parent
LEFT JOIN child1 ON child1.parentID = parent.parentID,
LEFT JOIN child2 ON child2.parentID = parent.parentID,
LEFT JOIN child3 ON child3.parentID = parent.parentID
WHERE parent.parentID = 1;

Is this the way it is intended? To my understanding the following happens here:

child1 joins parent, a new derived table DT1 gets created
child2 joins DT1 (and therefore parent and child1) and DT2 gets created
child3 joins DT2 (and therefore parent, child1 and child2)

But that feels somehow wrong as the child tables should only be connected (joined) to parent. There is no relation in between the child tables.

The result is as follows (if all child tables have 2 rows):

child1ID | child2ID | child3ID
   1     |    1     |    1    
   1     |    1     |    2    
   1     |    2     |    1    
   1     |    2     |    2    
   2     |    1     |    1    
   2     |    1     |    2    
   2     |    2     |    1    
   2     |    2     |    2

So we have 8 rows (2 x 2 x 2). With more rows and more child tables the count can easily go into millions of rows. But I want a result more like this:

child1ID | child2ID | child3ID
    1    |   NULL   |   NULL    
    2    |   NULL   |   NULL    
  NULL   |     1    |   NULL    
  NULL   |     2    |   NULL    
  NULL   |   NULL   |    1    
  NULL   |   NULL   |    2

Here we have only 6 rows (2 + 2 + 2) and even with more child tables the row count won't explode.

How can I achieve this?

By the way, I also looked at UNION and to use 3 selects, but that doesnt work as the table structure of the child tables is not identical (different column count and data types) Also GROUP BY seems not to be a good way as the query would still fetch millions of rows before the grouping happens.

Questioner

Andreas

Viewed

Original

SELECT T1.child1ID, T2.child2ID, T3.child3ID FROM parent LEFT JOIN (SELECT *, ROW_NUMBER() OVER() as rownum FROM child1) as T1 ON T1.parentID = parent.parentID LEFT JOIN (SELECT *, ROW_NUMBER() OVER() as rownum FROM child2) as T2 ON T2.parentID = parent.parentID AND T2.rownum = T1.rownum LEFT JOIN (SELECT *, ROW_NUMBER() OVER() as rownum FROM child3) as T3 ON T3.parentID = parent.parentID AND T3.rownum = T1.rownum WHERE parent.parentID = 1;

child1ID | child2ID | child3ID | child4ID | child5ID | child6ID 1 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | NULL | 2 | 2 | 3 | NULL | 3 | NULL | 3 | 3 | 4 | NULL | NULL | NULL | 4 | NULL | 5 | NULL | NULL | NULL | NULL | NULL |

Joining multiple child tables to one parent table WITHOUT getting all possible combinations

热门帖子

热门github