X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id AEFF6D1D21E for ; Thu, 29 Jan 2004 12:59:47 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 12802-06 for ; Thu, 29 Jan 2004 08:59:25 -0400 (AST) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 3DFC8D1CAD9 for ; Thu, 29 Jan 2004 08:59:16 -0400 (AST) Received: (qmail 9884 invoked by uid 500); 29 Jan 2004 13:00:41 -0000 Date: Thu, 29 Jan 2004 07:00:41 -0600 From: Bruno Wolff III To: Octavio Alvarez Cc: pgsql-sql@postgresql.org Subject: Re: LEFT JOIN on one and/or another column Message-ID: <20040129130041.GB17068@wolff.to> Mail-Followup-To: Octavio Alvarez , pgsql-sql@postgresql.org References: <1533.63.84.67.25.1075350420.squirrel@alvarezp.ods.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1533.63.84.67.25.1075350420.squirrel@alvarezp.ods.org> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/252 X-Sequence-Number: 16645 On Wed, Jan 28, 2004 at 20:27:00 -0800, Octavio Alvarez wrote: > > Hi. I have a table with two foreign keys (1 field each), like in > > id | serial > ext_key_original | integer > ext_key_exception | integer > > They mean different things, as one refers to a typical value, and the > other one refers to an exception that applies for that tuple. > > Each key references a different table, one with typical data, and one with > exception data. > > I'd like to join on the original, except when exception <> NULL, but I > can't quite figure out how to do so. I am not completely sure from your description what exactly you are trying to do, but it sounds like you can left join your table to the two foreign key tables and then use coallesce to grab the value from the appropiate joined table.