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 0528A529B8 for ; Fri, 5 Aug 2005 18:29:45 -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 54324-05 for ; Fri, 5 Aug 2005 21:29:40 +0000 (GMT) Received: from qtm.net (mummra.qtm.net [216.163.32.14]) by svr1.postgresql.org (Postfix) with SMTP id 3AC0A52854 for ; Fri, 5 Aug 2005 18:29:39 -0300 (ADT) Received: (qmail 49018 invoked from network); 5 Aug 2005 21:29:38 -0000 Received: from unknown (HELO ?192.168.0.110?) (mur@216.163.42.222) by mummra.qtm.net with SMTP; 5 Aug 2005 21:29:38 -0000 Message-ID: <42F3DAAA.4030306@endpoint.com> Date: Fri, 05 Aug 2005 17:31:22 -0400 From: Jeff Boes Organization: End Point User-Agent: Mozilla Thunderbird 1.0.5 (Windows/20050711) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: outer joins References: In-Reply-To: X-Enigmail-Version: 0.92.0.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.061 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200508/71 X-Sequence-Number: 18390 D Kavan wrote: > 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) > It would return 66, if all 66 p.project_id's were _missing_ from the set of pm.project_id's. A "LEFT JOIN" asks for all the rows in the left-hand table (p), joined to all the matching or missing rows in the right-hand table (pm). Then your "WHERE" clause filters all those but the missing ones OR the ones with global_id 2915. You need to check on the data with something like: select distinct project_id from project; select distinct project_id from project_members;