From: Jasen Betts <jasen@xnet.co.nz>
To: pgsql-sql@postgresql.org
Subject: Re: subselect and left join not working?
Date: 29 Nov 2010 09:44:44 GMT
Message-ID: <icvsmc$6p1$1@reversiblemaps.ath.cx> (raw)
References: <749730.36457.qm@web113802.mail.gq1.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasen@xnet.co.nz
Subject: Re: subselect and left join not working?
In-Reply-To: <icvsmc$6p1$1@reversiblemaps.ath.cx>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox