Combining a parent table with multiple child tables

I am struggling to find a way to combine multiple tables (through a view) to get the desired result.

I've found this Stack Overflow Q & A, which got me startied, but I can't get the result I need.

There is a parent table and multiple child tables, which have a child_id that corresponds to a parent_id. However, each child has some matching fields and some unique fields, which I'd like to combine all together.

Parent

+-----------+----------+
| parent_id | name     |
+-----------+----------+
|         1 | Bob      |
|         2 | Peter    |
|         3 | Alice    |
+-----------+----------+

Child 1

+----------+------------+-------+
| child_id | date       | color |
+----------+------------+-------+
|        1 | 2017-07-20 | red   |
|        3 | 2017-07-24 | blue  |
+----------+------------+-------+

Child 2

+----------+------------+--------+
| child_id | date       | lenght |
+----------+------------+--------+
|        2 | 2017-07-21 | 185    |
+----------+------------+--------+

Combined desired result

+-----------+-------+----------+------------+-------+--------+
| parent_id | name  | child_id | date       | color | lenght |
|         1 | Bob   |        1 | 2017-07-20 | red   | NULL   |
|         2 | Peter |        2 | 2017-07-21 | NULL  | 185    |
|         3 | Alice |        3 | 2017-07-24 | blue  | NULL   |
+-----------+-------+----------+------------+-------+--------+

You need outer joins. Specifically to LEFT JOIN the parent table with each child:

parent
LEFT JOIN child1 AS c1 ON p.parent_id = c1.child_id
LEFT JOIN child2 AS c2 ON p.parent_id = c2.child_id

and then SELECT from the appropriate tables. Columns that are in both children tables can be combined with COALESCE(). The order of the expressions inside the function matter only if there are cases where an id appears in both childen.

The query will be something like:

SELECT 
    p.parent_id, p.name,
    COALESCE(c1.child_id, c2.child_id) AS child_id,
    COALESCE(c1.date, c2.date) AS date, 
    c1.color, 
    c2.lenght
FROM
    parent AS p
  LEFT JOIN child1 AS c1 ON p.parent_id = c1.child_id
  LEFT JOIN child2 AS c2 ON p.parent_id = c2.child_id ;

StackExchange Database Administrators Q#181848, answer score: 3