Sqlite : ambiguous column name

sqlite ambiguous
user3459402 picture user3459402 · Mar 25, 2014 · Viewed 26.3k times · Source

I'm a newbie and i try to do that on my database

SELECT id FROM import a INNER JOIN import b ON a.id-1 =b.id AND b.val =0 WHERE a.val=-1

Pb : ambiguous column name: id

My table :

CREATE TABLE "import" (
    "id" INTEGER PRIMARY KEY  NOT NULL ,
    "id_analyse" integer, 
    "cross" varchar,
    "date" datetime,
    "close" double,
    "low" double,
    "high" double,
    "T" integer DEFAULT (NULL) ,
    "B" INTEGER
 )

I can't understand because i read

When more than one table is used (in a JOIN for example) there may be two columns with the same name.

And i use only one table !

is there somebody who can help me ?

stephane

Answer

laalto picture laalto · Mar 25, 2014

Just specify which id you want to select, e.g

SELECT a.id ....

Also your table does not seem to have the val column you're using later in the query.

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How to list the tables in a SQLite database file that was opened with ATTACH?

What SQL can be used to list the tables, and the rows within those tables in an SQLite database file - once I have attached it with the ATTACH command on the SQLite 3 command line tool?

Read More
How to get a list of column names on Sqlite3 database?

I want to migrate my iPhone app to a new database version. Since I don't have some version saved, I need to check if certain column names exist. This Stackoverflow entry suggests doing the select SELECT sql FROM sqlite_master …

Read More