Обсуждение: 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 -- Marco Lazzeri [ n o z e S.r.l. ] Via Giuntini, 25/29 - 56023 Navacchio - Cascina (PI) Tel +39 (0)50 754380 - Fax +39 (0)50 754381 mailto:marco.lazzeri@noze.it - http://www.noze.it
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? > 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) > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
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: > 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? > > > 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)
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: 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
> -----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.