A bare field name is legal in SQL only when it is unique; otherwise it needs to be qualified by table name or table alias. In a self join, the table name is identical, so using aliases is mandatory.
Result column names are not well defined in the SQL standard (apart from the use of the AS clause in the SELECT list), but most SQL engines will attempt to use something from either the SELECT statement itself (e.g. the text of an expression), or the table declaration. Result column names are not required to be unique, nor are they guranteed not to change between releases of the underlying SQL engine. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Rob Golsteijn Gesendet: Dienstag, 14. November 2017 16:38 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] Error: ambiguous column name Hi List, Given a table created as: create table aaa(a); Sqlite reports an error "ambiguous column name: main.aaa.a" for the following query. select * from aaa, aaa; Error: ambiguous column name: main.aaa.a And also for similar queries select * from aaa INNER JOIN aaa; select * from aaa CROSS JOIN aaa; select * from aaa JOIN aaa; Tested with sqlite version 3.21.0 and an old version 3.8.4.3. I think the query is valid and should not result in an error. Typically Sqlite would name the resulting columns "a" and "a:1" in this case. Workaround: add an alias for one of the tables in the join (both columns will be called "a"). Met Vriendelijke Groet, Kind Regards, 谨致问候, Rob Golsteijn _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
