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
