I get the same error in 3.6.18, so probably the same solution applies in 3.6.20. I got the query to work with a sub-select.
SQLite version 3.6.18 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> BEGIN TRANSACTION; sqlite> CREATE TABLE basica( ...> x, ...> y, ...> suma ...> ); sqlite> INSERT INTO "basica" VALUES('Austria','1996-03',5904.0); sqlite> INSERT INTO "basica" VALUES('Austria','1996-04',21904.0); sqlite> INSERT INTO "basica" VALUES('Germany','1996-03',10545.0); sqlite> INSERT INTO "basica" VALUES('Germany','1996-04',13687.0); sqlite> INSERT INTO "basica" VALUES('USA','1996-03',21814.0); sqlite> INSERT INTO "basica" VALUES('USA','1996-04',13108.0); sqlite> sqlite> CREATE TABLE groupLimX( ...> x, ...> sumaXs ...> ); sqlite> INSERT INTO "groupLimX" VALUES('USA',305843.0); sqlite> INSERT INTO "groupLimX" VALUES('Germany',258820.0); sqlite> INSERT INTO "groupLimX" VALUES('Austria',140668.0); sqlite> sqlite> CREATE TABLE groupLimY( ...> y, ...> sumaYs ...> ); sqlite> INSERT INTO "groupLimY" VALUES('1996-04',113818.0); sqlite> INSERT INTO "groupLimY" VALUES('1996-03',102947.0); sqlite> COMMIT; sqlite> sqlite> .header on sqlite> SELECT * FROM groupLimY INNER JOIN groupLimX; y|sumaYs|x|sumaXs 1996-04|113818.0|USA|305843.0 1996-04|113818.0|Germany|258820.0 1996-04|113818.0|Austria|140668.0 1996-03|102947.0|USA|305843.0 1996-03|102947.0|Germany|258820.0 1996-03|102947.0|Austria|140668.0 sqlite> CREATE TABLE mia AS SELECT * FROM groupLimY INNER JOIN groupLimX; sqlite> SELECT * FROM mia LEFT JOIN basica USING (y, x); y|sumaYs|x|sumaXs|suma 1996-04|113818.0|USA|305843.0|13108.0 1996-04|113818.0|Germany|258820.0|13687.0 1996-04|113818.0|Austria|140668.0|21904.0 1996-03|102947.0|USA|305843.0|21814.0 1996-03|102947.0|Germany|258820.0|10545.0 1996-03|102947.0|Austria|140668.0|5904.0 sqlite> SELECT * FROM (groupLimY INNER JOIN groupLimX) LEFT JOIN basica USING (y,x); SQL error: cannot join using column y - column not present in both tables sqlite> SELECT * FROM (select x, sumaXs, y, sumaYs from groupLimY INNER JOIN groupLimX) LEFT JOIN basica USING (y,x); x|sumaXs|y|sumaYs|suma USA|305843.0|1996-04|113818.0|13108.0 Germany|258820.0|1996-04|113818.0|13687.0 Austria|140668.0|1996-04|113818.0|21904.0 USA|305843.0|1996-03|102947.0|21814.0 Germany|258820.0|1996-03|102947.0|10545.0 Austria|140668.0|1996-03|102947.0|5904.0 sqlite> sqlite> SELECT * FROM (select * from groupLimY INNER JOIN groupLimX) LEFT JOIN basica USING (y,x); y|sumaYs|x|sumaXs|suma 1996-04|113818.0|USA|305843.0|13108.0 1996-04|113818.0|Germany|258820.0|13687.0 1996-04|113818.0|Austria|140668.0|21904.0 1996-03|102947.0|USA|305843.0|21814.0 1996-03|102947.0|Germany|258820.0|10545.0 1996-03|102947.0|Austria|140668.0|5904.0 sqlite> Ron Wilson, Engineering Project Lead (o) 434.455.6453, (m) 434.851.1612, www.harris.com HARRIS CORPORATION | RF Communications Division assuredcommunications(tm) > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of javaj1...@elxala.com > Sent: Tuesday, December 29, 2009 8:56 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] BUG Report on sqlite 3.6.20 "Error in SQL parser between > sqlite3.3.4 and sqlite3.6.20" > > Hello, > > I detect this problem because a program using sqlite command line works > on sqlite.3.3.4 but > it does not anymore using sqlite3.6.20 > > PROBLEM TITLE: Unjustified Error joining tables in sqlite3.6.20 (in > previous version sqlite3.3.4 OK) > OR Error in SQL parser between > sqlite3.3.4 and sqlite3.6.20 > > TESTED WITH: sqlite3.6.20 and sqlite3.3.4 Windows command line exes > > HOW TO REPRODUCE IT: > Execute following batch on both versions of sqlite > > sqlite3 < Fails3.6.20.sql > > when using 3.6.20 we get the "unjustified error" > > Best regards, > Alejandro > > > ------------Fails3.6.20.sql------------------ > BEGIN TRANSACTION; > CREATE TABLE basica( > x, > y, > suma > ); > INSERT INTO "basica" VALUES('Austria','1996-03',5904.0); > INSERT INTO "basica" VALUES('Austria','1996-04',21904.0); > INSERT INTO "basica" VALUES('Germany','1996-03',10545.0); > INSERT INTO "basica" VALUES('Germany','1996-04',13687.0); > INSERT INTO "basica" VALUES('USA','1996-03',21814.0); > INSERT INTO "basica" VALUES('USA','1996-04',13108.0); > > CREATE TABLE groupLimX( > x, > sumaXs > ); > INSERT INTO "groupLimX" VALUES('USA',305843.0); > INSERT INTO "groupLimX" VALUES('Germany',258820.0); > INSERT INTO "groupLimX" VALUES('Austria',140668.0); > > CREATE TABLE groupLimY( > y, > sumaYs > ); > INSERT INTO "groupLimY" VALUES('1996-04',113818.0); > INSERT INTO "groupLimY" VALUES('1996-03',102947.0); > COMMIT; > > .header on > SELECT * FROM groupLimY INNER JOIN groupLimX; > CREATE TABLE mia AS SELECT * FROM groupLimY INNER JOIN groupLimX; > SELECT * FROM mia LEFT JOIN basica USING (y, x); > > /* IN THIS LAST SELECT sqlite3 (3.6.20) FAILS!! WHILE sqlite 3.3.4 > SUCCESSED */ > SELECT * FROM (groupLimY INNER JOIN groupLimX) LEFT JOIN basica USING > (y, x); > ----------------------------------------------------------- > > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users