Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g17LV2E22054 for ; Thu, 7 Feb 2002 16:31:02 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 85123D5E5; Thu, 7 Feb 2002 13:31:03 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 7AC465BF8; Thu, 7 Feb 2002 13:31:03 -0800 (PST) Date: Thu, 7 Feb 2002 13:31:03 -0800 (PST) From: Stephan Szabo To: Frank Joerdens Cc: , , Subject: Re: Looking for the correct solution for a generic problem. In-Reply-To: <20020207215234.A6766@superfly.archi-me-des.de> Message-ID: <20020207132603.D32753-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200202/84 X-Sequence-Number: 6513 On Thu, 7 Feb 2002, Frank Joerdens wrote: > Now I want to retrieve rows from A as in > > SELECT DISTINCT A.foo > FROM A,B > WHERE A.foo [matches some criteria] > OR B.bar [matches some other criteria] > AND A.id = B.a_id; > > This works fine if there is *at least* one row in B for each row in A. > If there isn't, I obviously get 0 results even if the column foo in A > matches the desired criteria. But I do want the rows from A even if > there is no row in B that is linked to those rows in A that match the > criteria. If the column bar in B matches the desired criteria, I also > want the rows in A that are linked to those rows in B. Sounds like you want a left outer join. Maybe... select distinct a.foo from a left outer join b on (a.ID = b.a_id) where a.foo [matches] or B.bar [matches]