[sqlite] What is wrong with this simple SQL?

[Bart Smissaert]

This SQL gives me the error ambiguous column name: emis_number:

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 emis_number from DIABETICS)

If I use an alias in the last select:

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

This runs fine:

select emis_number from DIABETIC_ISSUES_LAST
where emis_number not in(select emis_number from DIABETICS)

So, how should I do this?


RBS