Re: If table A value IS NULL then table B

Il ven, 2004-01-23 alle 19:59, Ezra Epstein ha scritto:
> > 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)

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'?

Thank you!

Marco