Re: subselect and left join not working?

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(dot)arenas(at)kamarble(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > Jasen Betts <jasen(at)xnet(dot)co(dot)nz> writes:
> > > On 2010-11-29, Jorge Arenas <jorge(dot)arenas(at)kamarble(dot)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
>
>