By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,710 Members | 1,960 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,710 IT Pros & Developers. It's quick & easy.

If table A value IS NULL then table B

P: n/a
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:ma***********@noze.it - http://www.noze.it
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Mensaje citado por Marco Lazzeri <ma*******@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
-------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #2

P: n/a
On Fri, Jan 23, 2004 at 05:15:56PM -0300, Martín Marqués wrote:
Mensaje citado por Marco Lazzeri <ma*******@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 ma*******@postgresql.org)

Nov 22 '05 #3

P: n/a
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 <ma*******@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 ma*******@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #4

P: n/a
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, vh*****@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 <ma*******@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)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.