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: Friday, January 23, 2004 10:02 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] If table A value IS NULL then table B
>
>
> I've got a table called 'main' described as follow
>
> CREATE TABLE main (
>   id_other_table INT,
>   value CHAR
> );
>
> and a table called 'other' described as follow
>
> CREATE TABLE other (
>   id INT PRIMARY KEY,
>   value CHAR
> );
>
> I want to write a query on table 'main' that if 'id_other_table' is null
> returns value from itself, from table 'other' otherwise.
>
> Thank you very much, have a wonderful day!
>
> Marco
>

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)

== Ezra Epstein