Yuki Minami Yuki Minami - 20 days ago 8
SQL Question

Return data from multiple child tables in same row

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?

Answer

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;
Comments