Return data from multiple child tables in same row

Question

I've some tables related by foreign keys from where I need to get data from, their structure is something like:

    Table A                Intermediate A              Child A
| id |   Data          | idTableA | idChildA        | id | Child A
| 1  | 'Data 1'        | 1        | 1               | 1  | 'Child 1'
| 2  | 'Data 2'        | 1        | 2               | 2  | 'Child 2'
| 3  | 'Data 3'        | 1        | 3               | 3  | 'Child 3'
                       | 2        | 4               (...)
                       | 2        | 5
                       | 3        | 6
                       | 3        | 6

                           Intermediate B              Child B
                       | idTableA | idChildB        | id | Child B
                       | 1        | 4               | 4  | 'Child 1'
                       | 1        | 5               | 5  | 'Child 2'
                       | 1        | 6               | 6  | 'Child 3'
                       | 2        | 6               (...)
                       | 2        | 7
                       | 3        | 8
                       | 3        | 9

What I'm trying to do is to get all the records from both child tables that coincide with Table A key in the intermediate tables but without combining the data. Something like this:

                  Result
|idTableA|ChildA  |Child B |
|1       |'Child1'|null    |
|1       |'Child2'|null    |
|1       |'Child3'|null    |
|1       |null    |'Child4'|
|1       |null    |'Child5'|
|1       |null    |'Child6'|
|2       |'Child4'|null    |
|2       |'Child5'|null    |
|2       |null    |'Child6'|
|2       |null    |'Child7'|
(...)

I've been applying to intermediate tables and from there to child tables only to unsuccessfully retrieve mixed data from child tables where I'm supposed to get null values.

Any ideas?

Do you also want to include rows from Table A with *no* related row in either child table? — Erwin Brandstetter, Nov 12 '16 at 05:23

Patrick · Accepted Answer · 2016-11-12T04:03:32.887

2

You should select the rows from the separate child tables and then do a UNION between those two clauses, specifying NULL for the fields from the other child table:

SELECT main1.id AS idTableA, ca.childA, NULL::text AS childB
FROM tableA main1
JOIN intermediateA ia ON main1.id = ia.idTableA
JOIN childA ca ON ca.id = ia.idChildA

UNION

SELECT main2.id AS idTableA, NULL, cb.childB
FROM tableA main2
JOIN intermediateB ib ON main2.id = ib.idTableA
JOIN childB cb ON cb.id = ib.idChildB

ORDER BY 1, 2, 3;

edited Nov 12 '16 at 04:03

answered Nov 12 '16 at 03:57

Patrick

29,357
6
62
90

Finally could solve this problem after implementing your query. Thank you so much!. – Yuki Minami Nov 14 '16 at 17:13

score 1 · Answer 2 · edited May 23 '17 at 12:24

1

What @Patrick says.
But unless you ...

want to include rows from Table A with no related row in either child table
or you want to eliminate idTableA values not present in Table A (not possible with FK constraint)

... you don't need to include Table A in the query at all. (Else you need a LEFT JOIN.)

SELECT ia.idTableA, ca.childA, NULL::text AS childB
FROM   intermediateA ia
JOIN   childA ca ON ca.id = ia.idChildA

UNION ALL  -- ! 

SELECT ib.idTableA, NULL, cb.childB
FROM   intermediateB ib
JOIN   childB cb ON cb.id = ib.idChildB

ORDER   BY 1, 2, 3;

And make that UNION ALL. Cheaper, and you do not want to attempt to fold duplicates here.

edited May 23 '17 at 12:24

Community

1
1

answered Nov 12 '16 at 05:37

Erwin Brandstetter

605,456
145
1,078
1,228

I guess you are using `UNION ALL` like an optimizer hint ("Move along please, nothing to see here") but have you considered that the human reader needs to unpick this too? I see `UNION ALL` as a way of creating a resultset with duplicate rows but I never want to do that! So I always use `UNION`. If this is expensive then I'm worth it, build me a smarter optimizer ...but before you do, prove to me that `UNION ALL` is "cheaper". – onedaywhen Nov 14 '16 at 09:06
@onedaywhen: `UNION ALL` is obviously faster, just try for yourself. More importantly, if duplicate rows are not supposed to be eliminated, `UNION` is *wrong*, plain and simple. Insisting on it because you like the way it *looks* is ... well, not going anywhere. That's like insisting on a simple `JOIN` where the logic requires a `LEFT JOIN`, just because it's easier on the human eye. Even if it doesn't make a difference with the current data set, it's a bug waiting to happen. What if the column `Child A` isn't defined `UNIQUE` (by mistake)? – Erwin Brandstetter Nov 14 '16 at 16:29
It's not about cosmetics lol, It's about the relational model. There are no duplicate tuples in RM, even in derived relations (query results), so I have no use case for `UNION ALL` in SQL. There are no nulls in RM, so I have no use case for `LEFT JOIN` in SQL. – onedaywhen Nov 15 '16 at 08:37
@onedaywhen: `LEFT JOIN` can produce NULL values without any NULL in your model. – Erwin Brandstetter Nov 15 '16 at 16:32
Indeed, which is why `LEFT JOIN` should be avoided (or if it is used, the nulls it would produce are not projected in the result). – onedaywhen Nov 16 '16 at 11:56

Return data from multiple child tables in same row

2 Answers2