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-users > >> > > _______________________________________________ > > 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 >
