X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 820DB9DCBBC for ; Tue, 6 Dec 2005 11:00:56 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 24498-01 for ; Tue, 6 Dec 2005 11:01:00 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from pjetlic.mobart.hr (pjetlic.mobart.hr [80.80.51.10]) by postgresql.org (Postfix) with ESMTP id B5D119DCB9C for ; Tue, 6 Dec 2005 11:00:53 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by pjetlic.mobart.hr (Postfix) with ESMTP id CBA7B17A0E; Tue, 6 Dec 2005 16:00:57 +0100 (CET) Received: from pjetlic.mobart.hr ([127.0.0.1]) by localhost (pjetlic [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 02757-06-12; Tue, 6 Dec 2005 16:00:57 +0100 (CET) Received: from mike.mobart.hr (mike.mobart.hr [192.168.10.94]) (using SSLv3 with cipher RC4-MD5 (128/128 bits)) (No client certificate requested) by pjetlic.mobart.hr (Postfix) with ESMTP id AFA1F17A09; Tue, 6 Dec 2005 16:00:57 +0100 (CET) Subject: Re: JOIN query not working as expected From: Mario Splivalo Reply-To: mario.splivalo@mobart.hr To: Tom Lane Cc: pgsql-sql@postgresql.org In-Reply-To: <164.1133881091@sss.pgh.pa.us> References: <1133878084.8161.22.camel@ekim> <164.1133881091@sss.pgh.pa.us> Content-Type: text/plain Organization: Mob-Art Date: Tue, 06 Dec 2005 16:00:58 +0100 Message-Id: <1133881258.8161.28.camel@ekim> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by OpenSource AV+AS Mail Server X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200512/41 X-Sequence-Number: 23583 On Tue, 2005-12-06 at 09:58 -0500, Tom Lane wrote: > Mario Splivalo writes: > > Now I want all services which didn't have any messages within certain > > period: > > pulitzer2=# select * from services where id not in (select distinct > > service_id from messages where receiving_time between '2005-10-01' and > > '2005-10-30'); > > (0 rows) > > > Why is that? > > Probably, you've got some NULLs in the messages.service_id column ... > try excluding those from the sub-select. Yes, I've discovered that later. Still, I'm confused with the 'biggies' at the bottom of my initial mail. I neglected to metion that I'm using postgresql 7.4.8. I discovered that I can create a FK on a column wich allows NULL values, and I can even insert rows with NULLs in FK column, although PK table where FK is pointing does not allow nuls. Is that 'by design', or...? Mike -- Mario Splivalo Mob-Art mario.splivalo@mobart.hr "I can do it quick, I can do it cheap, I can do it well. Pick any two."