lundi 9 novembre 2015

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

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!

Aucun commentaire:

Enregistrer un commentaire