> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of Marco Lazzeri
> Sent: Saturday, January 24, 2004 7:19 AM
> Cc: [EMAIL PROTECTED]
> 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.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
