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
