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.are...@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 <t...@sss.pgh.pa.us> wrote: > > > > Jasen Betts <ja...@xnet.co.nz> writes: > > > On 2010-11-29, Jorge Arenas <jorge.are...@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 > >
