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.