Join columns from more than 2 tables in sqlite and fill the missing values with null

Question

I have 5 table in a sqlite database and I want to combine specific columns to a new table (FULL JOIN). If one table contains a date that is not in another tables, it should be added to the result table and the other column values should be null. See example.

table_a

| myDate      | value_column |
|-------------|--------------|
| 2015-01-01  |      1       |
| 2015-01-03  |      3       |

table_b

| myDate      | value_column |
|-------------|--------------|
| 2015-01-01  |      1       |
| 2015-01-02  |      2       |

table_c

| myDate      | value_column |
|-------------|--------------|
| 2015-01-01  |      1       |
| 2015-01-04  |      4       |

table_d

| myDate      | value_column |
|-------------|--------------|
| 2015-01-01  |      1       |
| 2015-01-02  |      2       |

table_e

| myDate      | value_column |
|-------------|--------------|
| 2015-01-01  |      1       |
| 2015-01-03  |      3       |

Resulting table:

| myDate      | value_column_a | value_column_c | value_column_c | value_column_d | value_column_e |
|-------------|----------------|----------------|----------------|----------------|----------------|
| 2015-01-01  |      1         |      1         |      1         |      1         |      1         |
| 2015-01-02  |      null      |      2         |      null      |      2         |      null      |
| 2015-01-03  |      3         |      null      |      4         |      null      |      3         |
| 2015-01-04  |      null      |      null      |      null      |      null      |      null      |

Thanks in advance!

Possible duplicate of [FULL OUTER JOIN with sqlite](http://stackoverflow.com/questions/1923259/full-outer-join-with-sqlite) — Giorgos Betsos, Nov 10 '15 at 07:54
Thanks for the hint. But I still don't understand how to use it on more than two tables. ( I have read the comments). — Droider, Nov 10 '15 at 08:01

score 0 · Accepted Answer · answered Nov 10 '15 at 08:56

First collect all possible date values, then you can use those to do a LEFT JOIN with all the data tables:

SELECT myDate,
       table_a.value_column AS value_column_a,
       table_b.value_column AS value_column_b,
       table_c.value_column AS value_column_c,
       table_d.value_column AS value_column_d,
       table_e.value_column AS value_column_e
FROM (SELECT myDate FROM table_a UNION
      SELECT myDate FROM table_b UNION
      SELECT myDate FROM table_c UNION
      SELECT myDate FROM table_d UNION
      SELECT myDate FROM table_e)
LEFT JOIN table_a USING (myDate)
LEFT JOIN table_b USING (myDate)
LEFT JOIN table_c USING (myDate)
LEFT JOIN table_d USING (myDate)
LEFT JOIN table_e USING (myDate);

Join columns from more than 2 tables in sqlite and fill the missing values with null

1 Answers1

Linked