From: Yon Den Baguse Ngarso <yon@dugem.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: SELECT ... WHERE ... NOT IN (SELECT ...);
Date: Fri, 23 Aug 2002 01:28:35 -0700 (PDT)
Message-ID: <20020823082835.959193953@sitemail.everyone.net> (raw)
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 <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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: yon@dugem.com, tgl@sss.pgh.pa.us
Subject: Re: SELECT ... WHERE ... NOT IN (SELECT ...);
In-Reply-To: <20020823082835.959193953@sitemail.everyone.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox