Re: If table A value IS NULL then table B

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Marco Lazzeri
> Sent: Saturday, January 24, 2004 7:19 AM
> Cc: pgsql-general@postgresql.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.