Re: subselect and left join not working? - Mailing list pgsql-sql
| From | Carla |
|---|---|
| Subject | Re: subselect and left join not working? |
| Date | |
| Msg-id | AANLkTinx84a96OF-gWJAj1qZQwHptdzP9EtfHewJ-A8s@mail.gmail.com Whole thread Raw |
| In response to | Re: subselect and left join not working? (Jorge Arenas <jorge.arenas@kamarble.com>) |
| List | 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.
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