On 2010-11-29, Jorge Arenas <jorge.arenas@kamarble.com> wrote:
> select zona_id from zonas where zona_id not in (select zona_id from usuarios ####### #######
#######
> where per_id =2)
select 'FRED' from from usuarios where per_id =2
what'shappening is your not in subquery is being 'corrupted' by the
surrounding query, the expression zona_id is being replaced with the
value from the main query. so the inner query return multiple copies
of the value from the outer query and the not-in fails.
to avoid that confusion do it this way:
select zona_id as z from zonas where z not in (select zona_id fromusuarios where per_id =2)
or this way:
select zona_id from zonas where zona_id not in (select usuarios.zona_id fromusuarios where per_id =2)
or this way:
select zona_id from zonas where zona_id not in (select u.zona_id fromusuarios as u where per_id =2)
--
⚂⚃ 100% natural
