Sqlite : ambiguous column name

Question

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 1

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.

Answer 2

You are actually using two tables in your query, not one, although they both get their data from the same underlying physical table. The tables are called a and b. In this particular query it doesn't matter whether you do SELECT a.id or SELECT b.id because the values are always the same. But imagine you wrote ON a.id = b.id + 1 -- in that it would make a difference whether you SELECTed the id column from a or b.