Thread: If table A value IS NULL then table B

If table A value IS NULL then table B

From

Marco Lazzeri

Date:

23 January 2004, 17:02:18

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

Re: If table A value IS NULL then table B

From

Martín Marqués

Date:

23 January 2004, 19:23:44

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

Re: If table A value IS NULL then table B

From

Alvaro Herrera

Date:

23 January 2004, 19:29:16

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)

Re: If table A value IS NULL then table B

From

vhikida@inreach.com

Date:

23 January 2004, 19:46:16

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

Re: If table A value IS NULL then table B

From

Marco Lazzeri

Date:

24 January 2004, 13:08:20

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)

Re: If table A value IS NULL then table B

From

Marco Lazzeri

Date:

24 January 2004, 14:19:35

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

Re: If table A value IS NULL then table B

From

"Ezra Epstein"

Date:

31 January 2004, 01:46:54

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

Re: If table A value IS NULL then table B

From

"Ezra Epstein"

Date:

31 January 2004, 05:51:56

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