Re: subselect and left join not working?

On 2010-11-29, Jorge Arenas <jorge(dot)arenas(at)kamarble(dot)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 from
usuarios where per_id =2)

or this way:

select zona_id from zonas where zona_id not in (select usuarios.zona_id from
usuarios where per_id =2)

or this way:

select zona_id from zonas where zona_id not in (select u.zona_id from
usuarios as u where per_id =2)

--
⚂⚃ 100% natural