Re: subselect and left join not working?

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