sqlite LEFT OUTER JOIN multiple tables

Question

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 ?

Never used sqlite3, but the correct standard SQL syntax is LEFT OUTER JOIN ... ON ... LEFT OUTER JOIN ... ON ..., and not nested joins. ) — raina77ow, Jun 19 '12 at 17:10
I have used this syntax on Oracle, PostgreSQL and SQLServer without any problem. — ferpega, Jun 19 '12 at 17:12
Nested joins - or `JOIN (one JOIN two JOIN three) ON (one.id = two.id AND two.some_id = three.some_id)`? — raina77ow, Jun 19 '12 at 17:15

score 33 · Accepted Answer · edited Jun 05 '16 at 15:19

33

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.

edited Jun 05 '16 at 15:19

animuson

53,861
28
137
147

answered Jun 19 '12 at 17:15

Nathaniel Ford

20,545
20
91
102

1

Thanks Nathaniel it runs fine. I have used previous sql format for years. But SQLite doesn't like it to much. – ferpega Jun 19 '12 at 17:26
It's possible that specific (fancy?) implementations are able to parse it, but that SQLite just holds to the spec. – Nathaniel Ford Jun 19 '12 at 17:29

sqlite LEFT OUTER JOIN multiple tables

1 Answers1

Linked