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.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
