X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AF8F652A2F for ; Fri, 5 Aug 2005 19:00:33 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 59471-10 for ; Fri, 5 Aug 2005 22:00:27 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 7413F52800 for ; Fri, 5 Aug 2005 19:00:24 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id D51B135360; Fri, 5 Aug 2005 15:00:24 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id ABF973534C; Fri, 5 Aug 2005 15:00:24 -0700 (PDT) Date: Fri, 5 Aug 2005 15:00:24 -0700 (PDT) From: Stephan Szabo To: D Kavan Cc: pgsql-admin@postgresql.org Subject: Re: outer joins In-Reply-To: Message-ID: <20050805145747.W97145@megazone.bigpanda.com> References: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200508/72 X-Sequence-Number: 18391 On Fri, 5 Aug 2005, D Kavan wrote: > > Hi, > > Thanks for the reply. That didn't help him, but he asked me to post this. > > There are 66 project id's in the project table and 3 rows in the > project_members table for global_id 2915, but it only returns 3. I would > think it should return 66 rows, with 63 of them having a null pm.project_id. > > SELECT > p.project_id, pm.project_id > FROM project p > LEFT OUTER JOIN project_members pm ON p.project_id = pm.project_id > where (pm.project_id is null or pm.global_id = 2915) That's not what that query does. That query matches up rows in project with rows in project_members based on project_id and then restricts to only those projects that didn't match or which matched to global_id=2915. I think what was desired can be gotten with something like: select p.project_id, pm.project_id from project p left outer join project_members pm on (p.project_id=pm.project_id and pm.global_id = 2915) Which I believe will extend a project with no matching global_id=2915 row with nulls.