Re: [SQL] subselect and left join not working?

2010-11-29 Thread Jasen Betts 
On 2010-11-29, Jorge Arenas  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

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] subselect and left join not working?

2010-11-29 Thread Tom Lane 
Jasen Betts  writes:
> On 2010-11-29, Jorge Arenas  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.

That explanation is nonsense, and so is the proposed fix.

What I suspect is really going on is that the subselect yields one or
more NULL values.  If there's a NULL then NOT IN can never return TRUE,
only FALSE (if the tested value is definitely present) or NULL (meaning
it might match one of the NULLs, because NULL means "unknown" in this
context).  Newbies get caught by that all the time :-( ... it's not one
of SQL's better features.

regards, tom lane

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] subselect and left join not working?

2010-11-29 Thread Jorge Arenas 
Tom,

The subselect worked when I removed nulls. Thanks! Now I am facing a similar
problem with the left join:

select zonas.zona_id from zonas order by zona_id
"A"
"B"
"C"
"D"
"DGO"
"E"
"F"
"F VER"
"FCOAH"
"FCHIH"
"FGRO"
"FGTO"
"FHGO"
"FPUE"
"FQRO"
"FQROO"
"FSLP"
"FYUC"
"JAL"
"MOR"
"T"
"x"


select zona_id, usr_folio from  usuarios where per_id = 2 order by
usuarios.zona_id

"A" 1002
"C" 1003
"D" 1004
"E" 1005
"F" 1006
"F VER" 1010
"FCHIH" 1007
"FPUE"  1009
"JAL"   1008
"x" 1000

select zonas.zona_id, usr_folio from zonas left join usuarios on
zonas.zona_id = usuarios.zona_id where per_id = 2 order by zonas.zona_id

"A" 1002
"C" 1003
"D" 1004
"E" 1005
"F" 1006
"F VER" 1010
"FCHIH" 1007
"FPUE"  1009
"JAL"   1008
"x" 1000

but I am expecting those zonas that have no usr_folio assignated

"A" 1002
"B"
"C" 1003
"D" 1004
"DGO"
"E" 1005
"F" 1006
"F VER" 1010
"FCOAH"
"FCHIH" 1007
"FGRO"
"FGTO"
"FHGO"
"FPUE"  1009
"FQRO"
"FQROO"
"FSLP"
"FYUC"
"JAL"   1008
"MOR"
"T"
"x" 1000

which can be achieve by doing:

select zona_id, usr_folio from  usuarios where per_id = 2
union
select zona_id,null from zonas where zona_id not in (select zona_id from
 usuarios where per_id = 2)
order by zona_id

but I would like to use the left join instead

thanks for your help

Jorge.















On Mon, Nov 29, 2010 at 9:27 AM, Tom Lane  wrote:
>
> Jasen Betts  writes:
> > On 2010-11-29, Jorge Arenas  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.
>
> That explanation is nonsense, and so is the proposed fix.
>
> What I suspect is really going on is that the subselect yields one or
> more NULL values.  If there's a NULL then NOT IN can never return TRUE,
> only FALSE (if the tested value is definitely present) or NULL (meaning
> it might match one of the NULLs, because NULL means "unknown" in this
> context).  Newbies get caught by that all the time :-( ... it's not one
> of SQL's better features.
>
>regards, tom lane