Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sqlite LEFT OUTER JOIN multiple tables

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 ?

like image 616
ferpega Avatar asked Jun 19 '12 17:06

ferpega


People also ask

Can you left join more than 2 tables?

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.

Does SQLite support left outer join?

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.

Can you Outer join 3 tables in SQL?

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.

How do I join two tables with left join in SQL?

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.


1 Answers

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.

like image 63
Nathaniel Ford Avatar answered Sep 23 '22 15:09

Nathaniel Ford