In this example we have 3 related tables on a SQLite database:
CREATE TABLE test1 (
c1 integer,
primary key (c1)
);
CREATE TABLE test2 (
c1 integer,
c2 integer,
primary key (c1, c2)
);
CREATE TABLE test3 (
c2 integer,
c3 integer,
primary key (c2)
);
Now I need to join all tables:
test1 -> test2 (with c1 column) test2 -> test3 (with c2 column).
I have tried this solution but it doesn't run:
SELECT
*
FROM test1 a
LEFT OUTER JOIN test2 b
LEFT OUTER JOIN test3 c
ON c.c2 = b.c2
ON b.c1=a.c1
It gives me an error:
near "ON": syntax error.
Any help ?
Sometimes you need to LEFT JOIN more than two tables to get the data required for specific analyses. Fortunately, the LEFT JOIN keyword can be used with multiple tables in SQL.
SQLite supports different types of SQL Joins, like INNER JOIN, LEFT OUTER JOIN, and CROSS JOIN. Each type of JOIN is used for a different situation as we will see in this tutorial.
Using JOIN in SQL doesn't mean you can only join two tables. You can join 3, 4, or even more! The possibilities are limitless.
The SQL LEFT JOIN (specified with the keywords LEFT JOIN and ON) joins two tables and fetches all matching rows of two tables for which the SQL-expression is true, plus rows from the frist table that do not match any row in the second table. SELECT * FROM table1 LEFT [ OUTER ] JOIN table2 ON table1. column_name=table2.
This is a simple misplacement of your ON
statement. This conforms to SQL standard:
SELECT *
FROM test1 a
LEFT OUTER JOIN test2 b ON b.c1=a.c1
LEFT OUTER JOIN test3 c ON c.c2=b.c2
This is explained in further depth here.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With