To: pgsql-sql@postgresql.org
Path: not-for-mail
From: Jasen Betts <jasen@xnet.co.nz>
Newsgroups: gmane.comp.db.postgresql.sql
Subject: Re: subselect and left join not working?
Date: 29 Nov 2010 09:44:44 GMT
Organization: Dis (not Dat) Organisation
Lines: 31
Message-ID: <icvsmc$6p1$1@reversiblemaps.ath.cx>
References: <749730.36457.qm@web113802.mail.gq1.yahoo.com>
NNTP-Posting-Host: localhost
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
User-Agent: slrn/pre1.0.0-18 (Linux)

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 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