pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsubselect and left join not working?
5+ messages / 4 participants
[nested] [flat]
* subselect and left join not working?
@ 2010-11-29 05:38 Jorge Arenas <jorge.arenas@kamarble.com>
2010-11-29 09:44 ` Re: subselect and left join not working? Jasen Betts <jasen@xnet.co.nz>
0 siblings, 1 reply; 5+ messages in thread
From: Jorge Arenas @ 2010-11-29 05:38 UTC (permalink / raw)
To: pgsql-sql
Hi,
I am using postgres 8.4 and I am having problems doing a very simple query:
select zona_id from zonas where zona_id not in (select zona_id from usuarios
where per_id =2)
but I get no records in return.
The problem is that, if I I break down the quey, I get:
select zona_id from zonas
"A"
"B"
"C"
"D"
"E"
"F"
"FCHIH"
"FCOAH"
"DGO"
"T"
"FGTO"
"FGRO"
"FQRO"
"FHGO"
"JAL"
"MOR"
"FPUE"
"FQROO"
"FSLP"
"F VER"
"FYUC"
""
select zona_id from usuarios where per_id =2
"E"
"F"
"FCHIH"
"JAL"
"FPUE"
"F VER"
"C"
"D"
"A"
""
but with
select zona_id from zonas where zona_id not in (select zona_id from usuarios
where per_id =2)
I dont' get records when I am expecting to see this:
"B"
"FCOAH"
"DGO"
"T"
"FGTO"
"FGRO"
"FQRO"
"FHGO"
"MOR"
"FQROO"
"FSLP"
"FYUC"
""
What's wrong? Please help. Any clues?
---
It also does not work a left join on 'zonas'. Some 'zonas' are designated to
some users but in my case is not wroking:
t1
usr zone
1 A
2 C
3 D
t2
zone
A
B
C
D
E
I want to see
zone usr
A 1
B
C 2
D 3
E
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: subselect and left join not working?
2010-11-29 05:38 subselect and left join not working? Jorge Arenas <jorge.arenas@kamarble.com>
@ 2010-11-29 09:44 ` Jasen Betts <jasen@xnet.co.nz>
2010-11-29 15:27 ` Re: subselect and left join not working? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Jasen Betts @ 2010-11-29 09:44 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: subselect and left join not working?
2010-11-29 05:38 subselect and left join not working? Jorge Arenas <jorge.arenas@kamarble.com>
2010-11-29 09:44 ` Re: subselect and left join not working? Jasen Betts <jasen@xnet.co.nz>
@ 2010-11-29 15:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-11-30 04:29 ` Re: subselect and left join not working? Jorge Arenas <jorge.arenas@kamarble.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2010-11-29 15:27 UTC (permalink / raw)
To: Jasen Betts <jasen@xnet.co.nz>; +Cc: Jorge Arenas <jorge.arenas@kamarble.com>; pgsql-sql
Jasen Betts <jasen@xnet.co.nz> writes:
> 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.
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: subselect and left join not working?
2010-11-29 05:38 subselect and left join not working? Jorge Arenas <jorge.arenas@kamarble.com>
2010-11-29 09:44 ` Re: subselect and left join not working? Jasen Betts <jasen@xnet.co.nz>
2010-11-29 15:27 ` Re: subselect and left join not working? Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-11-30 04:29 ` Jorge Arenas <jorge.arenas@kamarble.com>
2010-11-30 13:22 ` Re: subselect and left join not working? Carla <cgourofino@hotmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Jorge Arenas @ 2010-11-30 04:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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 <tgl@sss.pgh.pa.us> wrote:
>
> Jasen Betts <jasen@xnet.co.nz> writes:
> > 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.
>
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: subselect and left join not working?
2010-11-29 05:38 subselect and left join not working? Jorge Arenas <jorge.arenas@kamarble.com>
2010-11-29 09:44 ` Re: subselect and left join not working? Jasen Betts <jasen@xnet.co.nz>
2010-11-29 15:27 ` Re: subselect and left join not working? Tom Lane <tgl@sss.pgh.pa.us>
2010-11-30 04:29 ` Re: subselect and left join not working? Jorge Arenas <jorge.arenas@kamarble.com>
@ 2010-11-30 13:22 ` Carla <cgourofino@hotmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Carla @ 2010-11-30 13:22 UTC (permalink / raw)
To: Jorge Arenas <jorge.arenas@kamarble.com>; +Cc: pgsql-sql
Try it:
select zonas.zona_id, usr_folio from zonas left join usuarios on (*per_id =
2 and *zonas.zona_id = usuarios.zona_id) order by zonas.zona_id;
Or:
select zonas.zona_id, usr_folio from zonas left join usuarios on
zonas.zona_id = usuarios.zona_id where per_id = 2 *or usuarios.zona_id is
null* order by zonas.zona_id;
When you do a left join and a left table value does not match any value of
the right table, the left table's column will have a value and all of the
right table' columns will have NULL (inclusive per_id).
Carla O.
2010/11/30 Jorge Arenas <jorge.arenas@kamarble.com>
> 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 <tgl@sss.pgh.pa.us> wrote:
> >
> > Jasen Betts <jasen@xnet.co.nz> writes:
> > > 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.
> >
> > 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
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-11-30 13:22 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-29 05:38 subselect and left join not working? Jorge Arenas <jorge.arenas@kamarble.com>
2010-11-29 09:44 ` Jasen Betts <jasen@xnet.co.nz>
2010-11-29 15:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-11-30 04:29 ` Jorge Arenas <jorge.arenas@kamarble.com>
2010-11-30 13:22 ` Carla <cgourofino@hotmail.com>
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