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

Mensaje citado por Marco Lazzeri <marcomail@noze.it>:

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.

SELECT CASE WHEN id_other_table IS NULL THEN id_other_table
ELSE id
FROM main,other

Thank you very much, have a wonderful day!

Same for you.

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-------------------------------------------------------
Mart�n Marqu�s | Programador, DBA
Centro de Telem�tica | Administrador
Universidad Nacional
del Litoral
-------------------------------------------------------

On Fri, Jan 23, 2004 at 05:15:56PM -0300, Mart�n Marqu�s wrote:

Mensaje citado por Marco Lazzeri <marcomail@noze.it>:

I want to write a query on table 'main' that if 'id_other_table' is null
returns value from itself, from table 'other' otherwise.

SELECT CASE WHEN id_other_table IS NULL THEN id_other_table
ELSE id
FROM main,other

What about COALESCE?

SELECT COALESCE(id_other_table, id) FROM main, other WHERE ...

(Also probably an OUTER JOIN is needed -- see
http://www.varlena.com/GeneralBits/56.php)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Saca el libro que tu religi�n considere como el indicado para encontrar la
oraci�n que traiga paz a tu alma. Luego rebootea el computador
y ve si funciona" (Carlos Ducl�s)

As for the outer join, I think that the problem was ambiguous.

Is id_other_table a foreign key to id? Is there at most one row in each
table?

Yes, id_other_table IS a foreign key to id and usually I've got records
in each table.

Thanks to all of you!

Il ven, 2004-01-23 alle 21:45, vhikida@inreach.com ha scritto:

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

-----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.