Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2BDB5475A93 for ; Fri, 23 Aug 2002 04:28:35 -0400 (EDT) Received: from omta02.mta.everyone.net (sitemail3.everyone.net [216.200.145.37]) by postgresql.org (Postfix) with ESMTP id 7290A4759DF for ; Fri, 23 Aug 2002 04:28:34 -0400 (EDT) Received: from sitemail.everyone.net (dsnat [216.200.145.62]) by omta02.mta.everyone.net (Postfix) with ESMTP id A09F21C809E; Fri, 23 Aug 2002 01:28:35 -0700 (PDT) Received: by sitemail.everyone.net (Postfix, from userid 99) id 959193953; Fri, 23 Aug 2002 01:28:35 -0700 (PDT) Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 7bit Mime-Version: 1.0 X-Mailer: MIME-tools 5.41 (Entity 5.404) Date: Fri, 23 Aug 2002 01:28:35 -0700 (PDT) From: Yon Den Baguse Ngarso To: Tom Lane Cc: pgsql-sql@postgresql.org Subject: Re: SELECT ... WHERE ... NOT IN (SELECT ...); Reply-To: yon@dugem.com X-Originating-Ip: [202.152.11.250] Message-Id: <20020823082835.959193953@sitemail.everyone.net> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/321 X-Sequence-Number: 9167 Oops correction. If i create tbl1 & tbl2, and then load it with the data. The result is CORRECT. Like yours. But, if the data loaded from another table, the result become WRONG/ null record. Here is my detail step. Please Help. ---- myhost=# \d outages Table "outages" Attribute | Type | Modifier --------------------+--------------------------+---------- outageid | integer | not null losteventid | integer | regainedeventid | integer | myhost=# --create new temp tbl1 myhost=# SELECT losteventid AS eventid INTO tbl1 FROM outages; myhost=# --create new temp tbl2 myhost=# SELECT regainedeventid AS eventid INTO tbl2 FROM outages; myhost=# --check the new tbl myhost=# \d tbl1 Table "tbl1" Attribute | Type | Modifier -----------+---------+---------- eventid | integer | myhost=# SELECT eventid FROM tbl1; eventid --------- 119064 119064 60116 16082 16082 16303 16082 92628 92628 60083 (10 rows) myhost=# \d tbl2 Table "tbl2" Attribute | Type | Modifier -----------+---------+---------- eventid | integer | myhost=# SELECT eventid FROM tbl2; eventid --------- 123989 123989 123989 16134 16134 16368 16134 92685 92685 60115 (10 rows) myhost=# SELECT eventid FROM tbl1 WHERE eventid NOT IN (SELECT eventid FROM tbl2); eventid --------- (0 rows) TIA, Yon --- Yon Den Baguse Ngarso wrote: >That's way i'm confuse. >I'm using postgresql-7.1.3 > >I did the same action like you do. >Am i missing something? > >TIA, >Yon > >--- Tom Lane wrote: >>Yon Den Baguse Ngarso writes: >>> I'm confused, the result should not 0 rows, right? >> >>Not what I get: >> >>regression=# create table tbl1(eventid int); >>CREATE TABLE >>-- load data >>regression=# SELECT eventid FROM tbl1; >> eventid >>--------- >> 119064 >> 119064 >> 60116 >> 16082 >> 16082 >> 16303 >> 16082 >> 92628 >> 92628 >> 60083 >>(10 rows) >> >>regression=# create table tbl2(eventid int); >>CREATE TABLE >>-- load data >>regression=# SELECT eventid FROM tbl2; >> eventid >>--------- >> 123989 >> 123989 >> 123989 >> 16134 >> 16134 >> 16368 >> 16134 >> 92685 >> 92685 >> 60115 >>(10 rows) >> >>regression=# SELECT eventid FROM tbl1 WHERE eventid NOT IN (SELECT eventid FROM tbl2); >> eventid >>--------- >> 119064 >> 119064 >> 60116 >> 16082 >> 16082 >> 16303 >> 16082 >> 92628 >> 92628 >> 60083 >>(10 rows) >> >> >>I think there must be something you didn't tell us... >> >> regards, tom lane >> >>---------------------------(end of broadcast)--------------------------- >>TIP 6: Have you searched our list archives? >> >>http://archives.postgresql.org > >_____________________________________________________________ >Get yourname@dugem.com at http://www.dugem.com > >_____________________________________________________________ >Promote your group and strengthen ties to your members with email@yourgroup.org by Everyone.net http://www.everyone.net/?btn=tag > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to majordomo@postgresql.org so that your >message can get through to the mailing list cleanly _____________________________________________________________ Get yourname@dugem.com at http://www.dugem.com _____________________________________________________________ Promote your group and strengthen ties to your members with email@yourgroup.org by Everyone.net http://www.everyone.net/?btn=tag