Re: SELECT ... WHERE ... NOT IN (SELECT ...);

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(at)dugem(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>Yon Den Baguse Ngarso <yon(at)dugem(dot)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(at)dugem(dot)com at http://www.dugem.com
>
>_____________________________________________________________
>Promote your group and strengthen ties to your members with email(at)yourgroup(dot)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(at)postgresql(dot)org so that your
>message can get through to the mailing list cleanly

_____________________________________________________________
Get yourname(at)dugem(dot)com at http://www.dugem.com

_____________________________________________________________
Promote your group and strengthen ties to your members with email(at)yourgroup(dot)org by Everyone.net http://www.everyone.net/?btn=tag