0

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?

forpas
  • 160,666
  • 10
  • 38
  • 76
anni
  • 83
  • 5
  • Does this answer your question? [FULL OUTER JOIN with SQLite](https://stackoverflow.com/q/1923259/3404097) – philipxy Mar 31 '22 at 07:05

1 Answers1

1

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • Awesome I figured it out. To make it not re-list I just didn't add Table2 to the Union statement because it doesn't have unique ID's. Thank you. – anni Mar 30 '22 at 15:42
  • @anni if you are referring to the order of the returned rows you should know that only a ORDER BY clause guarantees a specific order. Without it, the system will pick the order that the optimizer used to execute the query. – forpas Mar 30 '22 at 15:46