From jorge.arenas@kamarble.com Mon Nov 29 05:45:41 2010 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 1FF8A1337B4F for ; Mon, 29 Nov 2010 01:45:41 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 50095-03 for ; Mon, 29 Nov 2010 05:45:34 +0000 (UTC) X-Greylist: delayed 00:06:43.219865 by SQLgrey-1.7.6 Received: from nm11.bullet.mail.sp2.yahoo.com (nm11.bullet.mail.sp2.yahoo.com [98.139.91.81]) by mail.postgresql.org (Postfix) with SMTP id 8DA8D1337B43 for ; Mon, 29 Nov 2010 01:45:33 -0400 (AST) Received: from [98.139.91.63] by nm11.bullet.mail.sp2.yahoo.com with NNFMP; 29 Nov 2010 05:38:49 -0000 Received: from [98.139.91.59] by tm3.bullet.mail.sp2.yahoo.com with NNFMP; 29 Nov 2010 05:38:49 -0000 Received: from [127.0.0.1] by omp1059.mail.sp2.yahoo.com with NNFMP; 29 Nov 2010 05:38:49 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 142526.56849.bm@omp1059.mail.sp2.yahoo.com Received: (qmail 37480 invoked by uid 60001); 29 Nov 2010 05:38:48 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1291009128; bh=eC6xEwN3sLJj78DrSD6Jcxl3yg47H6dB2P6AuNs80gY=; h=Message-ID:X-YMail-OSG:Received:X-RocketYMMF:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type; b=3vdCueVWhj2j5jnZkDxzLf0gBoSOtiHM3he25xv+BLJ7zw7JL/6dm9cfygKSRSXk++QnXW/IRNaIgvK70JcCHbM+VKd04s0ARf0b+AngyvsRM0dZVrQKZSJq5Jr7fmcbfkp+wQzsLjPDL87Um9FMczfyUa6enJ89mTv1sDErX4w= Message-ID: <749730.36457.qm@web113802.mail.gq1.yahoo.com> X-YMail-OSG: jZI43n0VM1kTtfo2orviKxkgvO8tvi9wFSlol.p8Px5rH4o mHEZWiQ.uVtndPAK6vtrUmnMO8iioZ.OQ2BkpGhVlF9915LJyfwObqTfVIaB 1Q8icBPmi4vivigecXplx8VFyDyydF4p2J9gyOCIO.uY3OrpsCTFnd1Yxmr7 hVuLHiGPbYZAWQ56gCWsnxIPPToI4LSIwEqdmvSTyxg5pANsFZxxycLdBb0q gYFwfgdRgh2SuUond0bl60j7wlLb.h1TJ.uWKMHMKYsFmlZdbVhmVZKB9u0k 0Rj8VhqKl1eDmuXMUioZsswcDE2KhCu4rFbBCJ8dQ9ZEohkpt12Yk.WbRVfF 1yA6w Received: from [99.16.198.177] by web113802.mail.gq1.yahoo.com via HTTP; Sun, 28 Nov 2010 21:38:48 PST X-RocketYMMF: jleviaguirre X-Mailer: YahooMailRC/553 YahooMailWebService/0.8.107.285259 Date: Sun, 28 Nov 2010 21:38:48 -0800 (PST) From: Jorge Arenas Subject: subselect and left join not working? To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-558874469-1291009128=:36457" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.467 tagged_above=-5 required=5 tests=BAYES_50=0.8, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, SARE_SPEC_LEO_LINE04=1.666 X-Spam-Level: ** X-Archive-Number: 201011/79 X-Sequence-Number: 34817 --0-558874469-1291009128=:36457 Content-Type: text/plain; charset=us-ascii 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 --0-558874469-1291009128=:36457 Content-Type: text/html; charset=us-ascii
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
--0-558874469-1291009128=:36457-- From jasen@xnet.co.nz Mon Nov 29 09:45:10 2010 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id DEFA81337B5E for ; Mon, 29 Nov 2010 05:45:10 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 08525-02 for ; Mon, 29 Nov 2010 09:45:04 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtpcorp.com (smtpcorp.com [67.192.120.76]) by mail.postgresql.org (Postfix) with ESMTP id 625251337B58 for ; Mon, 29 Nov 2010 05:45:02 -0400 (AST) DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=smtpcorp.com; s=main; h=Content-Transfer-Encoding:Content-Type:Mime-Version:References:Message-ID:Date:Subject:From:To; bh=nxyhu1BW89F8znyKlGn03VG5CIMvELf5dQuByNrGFY8=; b=dQlvytafO37TNq/YGuxwSKnXfjmFmVcXbW8PMYDQpcth1CgC11fLn1Ng/Ex2Wep9SXL3m1JL6sCxwxfe2W0BcqlHSbpsj8gmp6v+364yoPimiDhhoJUzwRahXvEOh8h+; Received: from ip-118-90-139-227.xdsl.xnet.co.nz ([118.90.139.227] helo=gonzo) by smtpcorp.com with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.70) id 1PN0I8-0006RU-Jh; mid 144 for pgsql-sql@postgresql.org; Mon, 29 Nov 2010 09:45:01 +0000 Received: from news by gonzo with local (Exim 4.72) (envelope-from ) id 1PN0Hs-0002NZ-Mv for pgsql-sql@postgresql.org; Mon, 29 Nov 2010 22:44:44 +1300 To: pgsql-sql@postgresql.org Path: not-for-mail From: Jasen Betts Newsgroups: gmane.comp.db.postgresql.sql Subject: Re: subselect and left join not working? Date: 29 Nov 2010 09:44:44 GMT Organization: Dis (not Dat) Organisation Lines: 31 Message-ID: References: <749730.36457.qm@web113802.mail.gq1.yahoo.com> NNTP-Posting-Host: localhost Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Trace: reversiblemaps.ath.cx 1291023884 6945 127.0.0.1 (29 Nov 2010 09:44:44 GMT) X-Face: ?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri*+R(&uv2=E1Q0Zk(>h!~o2ID@6{uf8s; a +M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W; (T"1(739R%m0Yyyv*gkGoPA.$b,D.w:z+<'"=-lVT?6 {T?=R^:W5g|E2#EhjKCa+nt":4b}dU7GYB*HBxn&Td$@f%.kl^:7X8rQWd[NTc"P"u6nkisze/Q; 8 "9Z{peQF,w)7UjV$c|RO/mQW/NMgWfr5*$-Z%u46"/00mx-,\R'fLPe.)^ User-Agent: slrn/pre1.0.0-18 (Linux) X-Smtpcorp-Track: 17070063.1.59196409 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.801 tagged_above=-5 required=5 tests=BAYES_50=0.8, RCVD_IN_SORBS_DUL=0.001 X-Spam-Level: X-Archive-Number: 201011/81 X-Sequence-Number: 34819 On 2010-11-29, Jorge Arenas 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 From tgl@sss.pgh.pa.us Mon Nov 29 15:27:10 2010 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 9705C1337B4F for ; Mon, 29 Nov 2010 11:27:10 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 17630-08 for ; Mon, 29 Nov 2010 15:27:05 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id F0C321337B93 for ; Mon, 29 Nov 2010 11:27:02 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id oATFR0sX025302; Mon, 29 Nov 2010 10:27:01 -0500 (EST) To: Jasen Betts cc: Jorge Arenas , pgsql-sql@postgresql.org Subject: Re: subselect and left join not working? In-reply-to: References: <749730.36457.qm@web113802.mail.gq1.yahoo.com> Comments: In-reply-to Jasen Betts message dated "29 Nov 2010 09:44:44 +0000" Date: Mon, 29 Nov 2010 10:27:00 -0500 Message-ID: <25301.1291044420@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201011/82 X-Sequence-Number: 34820 Jasen Betts writes: > On 2010-11-29, Jorge Arenas 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 From jorge.arenas@kamarble.com Tue Nov 30 04:54:53 2010 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 2F64D1337B50 for ; Tue, 30 Nov 2010 00:54:53 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 08764-04 for ; Tue, 30 Nov 2010 04:54:45 +0000 (UTC) X-Greylist: delayed 00:24:51.696692 by SQLgrey-1.7.6 Received: from mail-yx0-f174.google.com (mail-yx0-f174.google.com [209.85.213.174]) by mail.postgresql.org (Postfix) with ESMTP id 021A81337B4F for ; Tue, 30 Nov 2010 00:54:44 -0400 (AST) Received: by yxt3 with SMTP id 3so1985052yxt.19 for ; Mon, 29 Nov 2010 20:54:44 -0800 (PST) MIME-Version: 1.0 Received: by 10.150.138.13 with SMTP id l13mr11983389ybd.22.1291091392421; Mon, 29 Nov 2010 20:29:52 -0800 (PST) Received: by 10.150.138.3 with HTTP; Mon, 29 Nov 2010 20:29:52 -0800 (PST) In-Reply-To: <25301.1291044420@sss.pgh.pa.us> References: <749730.36457.qm@web113802.mail.gq1.yahoo.com> <25301.1291044420@sss.pgh.pa.us> Date: Mon, 29 Nov 2010 22:29:52 -0600 Message-ID: Subject: Re: subselect and left join not working? From: Jorge Arenas To: Tom Lane Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=000e0cd56b3ad161a904963da3ac X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201011/83 X-Sequence-Number: 34821 --000e0cd56b3ad161a904963da3ac Content-Type: text/plain; charset=ISO-8859-1 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 wrote: > > Jasen Betts writes: > > On 2010-11-29, Jorge Arenas 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 --000e0cd56b3ad161a904963da3ac Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Tom,

The subsel= ect 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"<= br style=3D"font-family: courier new,monospace;">"T"
"x"

select zona_id, usr_folio = from =A0usuarios where per_id =3D 2 order by usuarios.zona_id

"A"=A0=A0=A0=A0 1002
"C"=A0=A0=A0=A0 1003
"D"=A0=A0=A0= =A0 1004
"E"=A0=A0=A0=A0 1005
"F"=A0=A0=A0= =A0 1006
"F VER" 1010
"FCHIH" 1007<= /span>
"FPUE"=A0 1009
"JAL"=A0=A0 1= 008
"x"=A0=A0=A0=A0 1000<= br style=3D"font-family: courier new,monospace;">
select zonas.zona_id, usr_folio from zonas left = join usuarios on zonas.zona_id =3D usuarios.zona_id where per_id =3D 2 orde= r by zonas.zona_id

"A"=A0=A0=A0=A0 1002
"C"=A0=A0=A0=A0 1003
"D"=A0=A0=A0=A0 1004
"E"=A0=A0=A0=A0 1005
"F"=A0=A0=A0=A0 1006
"F VER" 1010
"FCHIH" 1007
"FPUE"= =A0 1009
"JAL"<= span style=3D"font-family: courier new,monospace;">=A0=A0 1008
"x"=A0=A0=A0=A0 1000

but I am expecting those zonas that have no usr_= folio assignated
"A"=A0=A0 =A0 1002
"B""C"=A0 =A0=A0 1003
"D" =A0 =A0 1004
"DGO"<= br style=3D"font-family: courier new,monospace;">"E"=A0=A0=A0=A0 1005
"F"=A0=A0=A0=A0 1006
"F VER" 1010<= /span>
"FCOAH"
"FCHIH" 1007<= /span>
"FGRO"
"FGTO"=
"FHGO"
"FPUE"=A0 100= 9
"FQRO"
"FQROO"
"FSLP"
"FYUC"=
"JAL"=A0=A0 1008
"MOR"<= br style=3D"font-family: courier new,monospace;">"T"
"x"=A0=A0=A0= =A0 1000

which can be achieve by doing:

select zona_id, usr_folio from =A0usuarios where= per_id =3D 2
union
select zona_id,null fro= m zonas where zona_id not in (select zona_id from =A0usuarios where per_id = =3D 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:
<= span style=3D"font-family: courier new,monospace;">>
> Jasen Betts <jasen@xnet.co.nz> writes:> > On 2010-11-29, Jorge Arenas <jorge.arenas@kamarble.com> wro= te:
> >> select zo= na_id from zonas where zona_id not in (select zona_id from usuarios<= br style=3D"font-family: courier new,monospace;">> > =A0 =A0 =A0 =A0 =A0####### =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0####### =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0#######
> >> where per= _id =3D2)
>
> > =A0select = 9;FRED' from from usuarios where per_id =3D2
>
> > what'shap= pening is your not in subquery is being 'corrupted' by the> > surrounding query, the expression zona_i= d is being replaced with the
> > value from th= e main query. so the inner query return multiple copies
> > of the value from the outer query and the not-in fa= ils.
>
> That explanation is nonsense, and so is the proposed fi= x.
>
> What I suspect is really going on is that the subselect= yields one or
> more NULL values. = =A0If there's a NULL then NOT IN can never return TRUE,
> only FALSE (if the tested value is definitely presen= t) or NULL (meaning
> it might match one= of the NULLs, because NULL means "unknown" in this
> context). =A0Newbies get caught by that all the ti= me :-( ... it's not one
> of SQL's bette= r features.
>
> =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane

--000e0cd56b3ad161a904963da3ac-- From cgourofino@hotmail.com Tue Nov 30 13:23:30 2010 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id E95A71337B80 for ; Tue, 30 Nov 2010 09:23:30 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 96611-06-5 for ; Tue, 30 Nov 2010 13:23:09 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-pv0-f174.google.com (mail-pv0-f174.google.com [74.125.83.174]) by mail.postgresql.org (Postfix) with ESMTP id C34F01337BB3 for ; Tue, 30 Nov 2010 09:22:43 -0400 (AST) Received: by pva4 with SMTP id 4so875291pva.19 for ; Tue, 30 Nov 2010 05:22:43 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:sender:reply-to:received :in-reply-to:references:date:x-google-sender-auth:message-id:subject :from:to:cc:content-type; bh=/CGENeu5diX61qRa2llFQTzImDeqCaci3uNKvv0eNlw=; b=CzyOICHhZl21q28QrKNYE8kxNapxYgSO+TKjXRZcawl0B6IvqGYexJK7d8W165Q7d4 RkHnH5cM10lo6WCuZ9dkqIosoKWvRIJ0+jp2BGFGdvIp8NKyUCtees3Sbs6IHT7zqS2c so4ywMJXdQQ/Ey6i1i+EQt2QHJq0X3JwX1IJI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:sender:reply-to:in-reply-to:references:date :x-google-sender-auth:message-id:subject:from:to:cc:content-type; b=h8hSEtRI8fuIQuSarMlVl3nLYoe1ItcexfiYWFnJImBOl1QvOoR/JqPsyAHxuuAz9P ePN9kZO03nCTve9Rk54+x62VxFIn9wijz1hd/k4ZQv2AMi7Bgh4ZKUYZN+L4482c+9gM hHXUs60NVpEV2jrdXbDxXVELMqJrQ0jNTM7Hk= MIME-Version: 1.0 Received: by 10.142.193.4 with SMTP id q4mr7087182wff.152.1291123363626; Tue, 30 Nov 2010 05:22:43 -0800 (PST) Reply-To: cgourofino@hotmail.com Received: by 10.142.177.14 with HTTP; Tue, 30 Nov 2010 05:22:43 -0800 (PST) In-Reply-To: References: <749730.36457.qm@web113802.mail.gq1.yahoo.com> <25301.1291044420@sss.pgh.pa.us> Date: Tue, 30 Nov 2010 11:22:43 -0200 X-Google-Sender-Auth: h0iR1Pjr6CEtESNBLKhCblO3Ts0 Message-ID: Subject: Re: subselect and left join not working? From: Carla To: Jorge Arenas Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=000e0cd32e20733fbc04964515d2 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.025 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FORGED_HOTMAIL_RCVD2=0.874, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201011/88 X-Sequence-Number: 34826 --000e0cd32e20733fbc04964515d2 Content-Type: text/plain; charset=ISO-8859-1 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 > 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 wrote: > > > > Jasen Betts writes: > > > On 2010-11-29, Jorge Arenas 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 > > --000e0cd32e20733fbc04964515d2 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Try it:

select zonas.zona_id, usr_folio from zonas left join usuario= s on (per_id =3D 2 and zonas.zona_id =3D usuarios.zona_id) order by = zonas.zona_id;

Or:

select zonas.zona_id, usr_folio from zonas= left join usuarios on zonas.zona_id =3D usuarios.zona_id where per_id =3D = 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 subsel= ect 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"<= br style=3D"font-family: courier new,monospace;">"T"
"x"

select zona_id, usr_folio = from =A0usuarios where per_id =3D 2 order by usuarios.zona_id

"A"=A0=A0=A0=A0 1002
"C"=A0=A0=A0=A0 1003
"D"=A0=A0=A0= =A0 1004
"E"=A0=A0=A0=A0 1005
"F"=A0=A0=A0= =A0 1006
"F VER" 1010
"FCHIH" 1007<= /span>
"FPUE"=A0 1009
"JAL"=A0=A0 1= 008
"x"=A0=A0=A0=A0 1000<= br style=3D"font-family: courier new,monospace;">
select zonas.zona_id, usr_folio from zonas left = join usuarios on zonas.zona_id =3D usuarios.zona_id where per_id =3D 2 orde= r by zonas.zona_id

"A"=A0=A0=A0=A0 1002
"C"=A0=A0=A0=A0 1003
"D"=A0=A0=A0=A0 1004
"E"=A0=A0=A0=A0 1005
"F"=A0=A0=A0=A0 1006
"F VER" 1010
"FCHIH" 1007
"FPUE"= =A0 1009
"JAL"<= span style=3D"font-family: courier new,monospace;">=A0=A0 1008
"x"=A0=A0=A0=A0 1000

but I am expecting those zonas that have no usr_= folio assignated
"A"=A0=A0 =A0 1002
"B""C"=A0 =A0=A0 1003
"D" =A0 =A0 1004
"DGO"<= br style=3D"font-family: courier new,monospace;">"E"=A0=A0=A0=A0 1005
"F"=A0=A0=A0=A0 1006
"F VER" 1010<= /span>
"FCOAH"
"FCHIH" 1007<= /span>
"FGRO"
"FGTO"=
"FHGO"
"FPUE"=A0 100= 9
"FQRO"
"FQROO"
"FSLP"
"FYUC"=
"JAL"=A0=A0 1008
"MOR"<= br style=3D"font-family: courier new,monospace;">"T"
"x"=A0=A0=A0= =A0 1000

which can be achieve by doing:

select zona_id, usr_folio from =A0usuarios where= per_id =3D 2
union
select zona_id,null fro= m zonas where zona_id not in (select zona_id from =A0usuarios where per_id = =3D 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 A= renas <jo= rge.arenas@kamarble.com> wrote:
> >> select zo= na_id from zonas where zona_id not in (select zona_id from usuarios<= br style=3D"font-family: courier new,monospace;">> > =A0 =A0 =A0 =A0 =A0####### =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0####### =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0#######
> >> where per= _id =3D2)
>
> > =A0select = 9;FRED' from from usuarios where per_id =3D2
>
> > what'shap= pening is your not in subquery is being 'corrupted' by the> > surrounding query, the expression zona_i= d is being replaced with the
> > value from th= e main query. so the inner query return multiple copies
> > of the value from the outer query and the not-in fa= ils.
>
> That explanation is nonsense, and so is the proposed fi= x.
>
> What I suspect is really going on is that the subselect= yields one or
> more NULL values. = =A0If there's a NULL then NOT IN can never return TRUE,
> only FALSE (if the tested value is definitely presen= t) or NULL (meaning
> it might match one= of the NULLs, because NULL means "unknown" in this
> context). =A0Newbies get caught by that all the ti= me :-( ... it's not one
> of SQL's bette= r features.
>
> =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane


--000e0cd32e20733fbc04964515d2--