Re: If table A value IS NULL then table B

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Marco Lazzeri
> Sent: Saturday, January 24, 2004 7:19 AM
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] If table A value IS NULL then table B
>
> > I think this post belongs on the SQL list, not the general list.
> >
> > Anyway, the SQL you want is:
> >
> > =$> select COALESCE(other.value, main.value) AS "value" from main left
> > outer join other ON main.id_other_table = other.id;
> >
> > For example, given:
> > insert into main (id_other_table, value) values (NULL, 'M');
> > insert into main (id_other_table, value) values (1, 'o');
> > insert into other (id, value) values (1, 'X');
> > The query returns:
> > value
> > -------
> > M
> > X
> > (2 rows)
>
> What if I would like to return more values from table 'other'?
> Your cool query just return 'other.value', what if I also need
> 'other.value_two'?
>

Then you would probably want a
SELECT CASE ...
which someone else posted as a reply. See:

http://www.postgresql.org/docs/7.4/static/functions-conditional.html
and
http://www.postgresql.org/docs/aw_pgsql_book/node44.html

NOTES:
1. If you do this query often, you can create a VIEW based on its results
(or write a set returning function).
2. BE CAREFUL If you return values from "other" that you do not return
from "main". In general a SQL Select should return the same tuple structure
(same class) all the time. So if it were getting values from "main" you
would likely want to return a null value...

Of course, if you don't like CASE statements, you can still do this with a
join:

=$> select COALESCE(other.value, main.value) AS "value",
COALESCE(other.value_two, NULL) AS "value_two" from main left outer join
other ON main.id_other_table = other.id;

The above 2 NOTES still apply. And, of course, since the second argument to
the 2nd coalesce function call is NULL, it is redundant, so you can just
write:

=$> select COALESCE(other.value, main.value) AS "value", other.value_two
from main left outer join other ON main.id_other_table = other.id;

== Ezra E.