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

You are using two tables, `a` and `b`, both of which have column `id`. — hamstergene, Mar 25 '14 at 11:34
In your query alias name a and b both for same table 'import', may this cause problem — Jaihind, Mar 25 '14 at 13:40

score 23 · Answer 1 · answered Mar 25 '14 at 11:34

23

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.

answered Mar 25 '14 at 11:34

laalto

150,114
66
286
303

score 6 · Answer 2 · answered Mar 25 '14 at 11:38

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.

score 2 · Answer 3 · edited Apr 30 '18 at 22:38

2

I had a case where the error happened in sqlite for query:

select * from A, B where A.col=B.col

The problem was that the col was present in both the tables A and B. This was fixed by specifically selecting all columns from table B: Select B.*...

edited Apr 30 '18 at 22:38

ascripter

5,665
12
45
68

answered Apr 30 '18 at 15:27

Srinivasan

399
1
2
9

score 1 · Answer 4 · answered Jan 26 '17 at 21:23

1

You may also get this error from an ambiguity in your WHERE clause. So, for example, instead of...

WHERE _id = 4

...you may need to put...

WHERE my_table_name._id = 4

answered Jan 26 '17 at 21:23

ban-geoengineering

18,324
27
171
253

score 0 · Answer 5 · answered Apr 22 '21 at 14:40

Another option is to declare field alias using AS keyword in the query.

SELECT import.id AS id, id_analyse, any_other_field FROM import WHERE ...

This is especially useful when you use JOIN where id field most probably would be ambiguous in different tables. The downside of this approach is that you have to enumerate all the necessary fields in the query but in some cases this might be particularly useful.

Sqlite : ambiguous column name

5 Answers5