CODEWITHSUNDEEP
sqlpostgresqljoinunionunion-all
Yuki Minami
Yuki Minami

Reputation: 35

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?

Upvotes: 2

Views: 257

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658092

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.

Upvotes: 1

Patrick
Patrick

Reputation: 32316

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;

Upvotes: 2

Related Questions