The sub-select is within an "inner namespace" to the whole query. You are free to reference fields defined in all tables occuring in the subselect's FROM list IN ADDITION TO any fields defined in tables occurring in the main query's FROM list. This is a requirement for correlated subqueries. When the SQLite parser detects an unqualified name, it will attempt to resolve the name by searching the whole namespace - failing in the example because there are 2 fields of the same name. A qualified name restricts the search to the defined database/table - failing inthe example because there is no field of the desired name there.
-----Urspr?ngliche Nachricht----- Von: Bart Smissaert [mailto:bart.smissaert at gmail.com] Gesendet: Sonntag, 22. M?rz 2015 15:49 An: General Discussion of SQLite Database Betreff: Re: [sqlite] What is wrong with this simple SQL? But I thought that as the non-aliased column emis_number is in the sub-select (select emis_number from DIABETICS) that column name could only apply to the table DIABETICS and there should be no ambiguity. RBS On Sun, Mar 22, 2015 at 2:33 PM, Ketil Froyn <ketil at froyn.name> wrote: > But both the patients table and the DIABETIC_ISSUES_LAST table have > columns called emis_number. Since your query turns out to be valid > despite not doing what you expected, sqlite doesn't know which of > those columns you're referring to. So it looks like the "ambiguous > column name" is in fact the correct error message. > > Regards, Ketil > > On 22 March 2015 at 15:15, Bart Smissaert <bart.smissaert at gmail.com> > wrote: > > Sorry, that table did indeed not have a column named emis_number, my > > mistake. > > Still, the error message ambiguous column name doesn't seem quite right. > > Should that not also be no such column: emis_number? > > > > RBS > > > > On Sun, Mar 22, 2015 at 2:06 PM, Igor Tandetnik <igor at tandetnik.org> > wrote: > > > >> On 3/22/2015 8:50 AM, Bart Smissaert wrote: > >> > >>> select g.gp_name, d.emis_number from DIABETIC_ISSUES_LAST d inner > >>> join patients p on(d.emis_number = p.emis_number) inner join > >>> gp_table g on(p.usual_gp_index_number = g.gp_id) where > >>> d.emis_number not in(select DB.emis_number from DIABETICS DB) > >>> > >>> I get: > >>> > >>> no such column: DB.emis_number > >>> > >> > >> So, the table DIABETICS doesn't have a column named emis_number > >> > >> This runs fine: > >>> > >>> select emis_number from DIABETIC_ISSUES_LAST where emis_number not > >>> in(select emis_number from DIABETICS) > >>> > >> > >> emis_number in the sub-select is DIABETIC_ISSUES_LAST.emis_number, > >> not DIABETICS.emis_number > >> > >> So, how should I do this? > >>> > >> > >> First, you have to figure out *what* you are trying to do. In light > >> for the fact that DIABETICS doesn't have a column named > >> emis_number, it's > not > >> at all clear. > >> -- > >> Igor Tandetnik > >> > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users at mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-user > >> s > >> > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > -- > -Ketil > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
