I’m trying to join multiple tables.
Table 1:
| ID | TEMP | DESC | NUMB |
|---|---|---|---|
| 32 | 89 | Y | 6 |
| 47 | NULL | Y | 5 |
| 56 | 43 | N | 4 |
| 34 | 54 | N | 3 |
| 22 | 78 | NULL | NULL |
Table 2
| ID | IND | FLAV |
|---|---|---|
| 32 | Y | G |
| 47 | N | G |
| 56 | Y | R |
| 34 | Y | B |
| 22 | Y | Y |
Table 3:
| ID | COLOR | SHAPE |
|---|---|---|
| 32 | RED | SQUARE |
| 47 | BLUE | CIRCLE |
| 52 | NULL | TRI |
| 22 | ORANGE | NULL |
I want the resulting table:
| ID | TEMP | DESC | NUMB | IND | FLAV | COLOR | SHAPE |
|---|---|---|---|---|---|---|---|
| 32 | 89 | Y | 6 | Y | G | RED | SQUARE |
| 47 | NULL | Y | 5 | N | G | BLUE | CIRCLE |
| 56 | 43 | N | 4 | Y | R | NULL | NULL |
| 34 | 54 | N | 3 | Y | B | NULL | NULL |
| 22 | 78 | NULL | NULL | Y | Y | ORANGE | NULL |
| 52 | NULL | NULL | NULL | NULL | NULL | NULL | TRI |
The row order of the resulting ID’s doesn’t matter to me.
I’ve tried:
SELECT *
FROM Table1
INNER JOIN Table2 USING(ID)
LEFT JOIN Table3 USING(ID)
But it leaves out ID 52. I want to be sure no unmatched ID’s from either table are left out.
Is this possible in SQLITE?
>Solution :
For this requirement the correct type of join is FULL OUTER JOIN wich is not supported by SQLite.
A workaround is to use a subquery that returns the distinct ids of all 3 tables and then do LEFT joins to the tables with the USING clause:
SELECT *
FROM (SELECT ID FROM Table1 UNION SELECT ID FROM Table2 UNION SELECT ID FROM Table3) t
LEFT JOIN Table1 USING (id)
LEFT JOIN Table2 USING (id)
LEFT JOIN Table3 USING (id);
See the demo.