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 89F299DCCC3
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  6 Dec 2005 10:55:29 -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 23477-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  6 Dec 2005 10:55:32 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from smtpout.eastlink.ca (smtpout.eastlink.ca [24.222.0.30])
	by postgresql.org (Postfix) with ESMTP id 8E52F9DCBE3
	for <pgsql-sql@postgresql.org>; Tue,  6 Dec 2005 10:55:26 -0400 (AST)
Received: from ip01.eastlink.ca ([24.222.10.5])
	by mta01.eastlink.ca (Sun Java System Messaging Server 6.2-4.03 (built
	Sep 22 2005)) with ESMTP id <0IR300CKI04H2H80@mta01.eastlink.ca> for
	pgsql-sql@postgresql.org; Tue, 06 Dec 2005 10:55:29 -0400 (AST)
Received: from blk-137-99-147.eastlink.ca (HELO [192.168.1.3])
	([24.137.99.147]) by ip01.eastlink.ca with ESMTP; Tue,
	06 Dec 2005 10:55:19 -0400
Date: Tue, 06 Dec 2005 10:55:12 -0400
From: Michael Burke <michael@engtech.ca>
Subject: Re: Database with "override" tables
In-reply-to: <EKEMKEFLOMKDDLIALABIAEMNCFAA.lvaningen@esncc.com>
To: Lane Van Ingen <lvaningen@esncc.com>
Cc: PGSQL-SQL <pgsql-sql@postgresql.org>
Message-id: <4395A650.9000607@engtech.ca>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT
X-Accept-Language: en
X-BrightmailFiltered: true
X-Brightmail-Tracker: AAAAAA==
References: <EKEMKEFLOMKDDLIALABIAEMNCFAA.lvaningen@esncc.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.12) Gecko/20051007
	Debian/1.7.12-1
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/39
X-Sequence-Number: 23581

Lane Van Ingen wrote:

>I think I have a similar situation involving the naming of assets, where
>the usual asset description is used, but users can enter a description in 
>a separate table which 'overrides' the original name with a name that is
>more familiar to the individual.
>
>IF THIS IS WHAT YOU WANT, it was accomplished by doing a UNION between two
>select statements, like this:
>  select <override values> from foo1
>  union
>  select <normal values> from foo2
>    where <record not in foo1>;
>
>Hope this helps.
>
>  
>
That almost works, and it is a much cleaner query than I had before.  
However, there's a possibility that some columns in the overridden table 
are NULL (indicating that the original value should be used).  So, a 
particular asset may contain a description and price; the price may be 
NULL, meaning the read-only value should be used, but the user may have 
attached a special description as we previously outlined.

What I'm looking for is the ability to, perhaps, "overlay" foo2 onto 
foo1, joined on foo1_id.  Then, NULL values in foo2 become "transparent" 
and we see the foo1 values behind them.

Presently I am using COALESCE() for every pair individually, ie. 
COALESCE(foo2.price, foo1.price), COALESCE(foo2.descr, foo1.descr), ... 
and then doing a FULL JOIN.  This works.  I'm starting to wonder if it's 
worth the extra hassle, I may just use your suggested UNION method instead.

Thanks again,
Mike.