Re: SELECT ... WHERE ... NOT IN (SELECT ...); - Mailing list pgsql-sql
| From | Yon Den Baguse Ngarso |
|---|---|
| Subject | Re: SELECT ... WHERE ... NOT IN (SELECT ...); |
| Date | |
| Msg-id | 20020823082835.959193953@sitemail.everyone.net Whole thread Raw |
| In response to | SELECT ... WHERE ... NOT IN (SELECT ...); (Yon Den Baguse Ngarso <yon@dugem.com>) |
| Responses |
Re: SELECT ... WHERE ... NOT IN (SELECT ...);
(Richard Huxton <dev@archonet.com>)
Re: SELECT ... WHERE ... NOT IN (SELECT ...); (Tom Lane <tgl@sss.pgh.pa.us>) Re: SELECT ... WHERE ... NOT IN (SELECT ...); ("Ross J. Reedstrom" <reedstrm@rice.edu>) |
| List | pgsql-sql |
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 nulllosteventid | 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 <yon@dugem.com> 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 <tgl@sss.pgh.pa.us> wrote: >>Yon Den Baguse Ngarso <yon@dugem.com> 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