CODEWITHSUNDEEP
sqliteambiguous
user3459402
user3459402

Reputation: 121

Sqlite : ambiguous column name

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

Upvotes: 12

Views: 47582

Answers (5)

Nikage
Nikage

Reputation: 588

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.

Upvotes: 0

Srinivasan
Srinivasan

Reputation: 469

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.*...

Upvotes: 3

Boycott A.I.
Boycott A.I.

Reputation: 18871

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

Upvotes: 1

Larry Lustig
Larry Lustig

Reputation: 50970

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.

Upvotes: 6

laalto
laalto

Reputation: 152817

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.

Upvotes: 24

Related Questions