Re: Database with "override" tables

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.