473,322 Members | 1,493 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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: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
4 2357
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: ajay | last post by:
I have following code for a slide menu but i twiked it to work for a single level menu. Open it in a Browser to get a clear picture. I have 2 Qs 1) How to make first entry as non-link. i.e i...
10
by: MLH | last post by:
I have an A97 table with a Yes/No field named TowJob and a form bound to that table. The TowJob control on the form is bound to the same field. It is an option group with Yes and No bttns valued...
4
by: Simone Battagliero | last post by:
I wrote a program which inserts and finds elements in an hash table. Each element of the table is a dinamic list, which holds all elements having the same hash value (calculated by an int...
21
by: Johan Tibell | last post by:
I would be grateful if someone had a minute or two to review my hash table implementation. It's not yet commented but hopefully it's short and idiomatic enough to be readable. Some of the code...
7
oll3i
by: oll3i | last post by:
i want to change the values in two columns one colum is a combobox and the secons column is editable too i want to get the value of that second column and the value of combobox and sent that...
9
by: dli07 | last post by:
Hello, I'm trying to convert a piece of code that creates a dynamic vertical resizing bar in a table from internet explorer to firefox. It's based on a post from...
1
by: since | last post by:
I figured I would post my solution to the following. Resizable column tables. Search and replace values in a table. (IE only) Scrollable tables. Sortable tables. It is based on a lot...
6
by: Romulo NF | last post by:
Greetings again to everyone, Im back to show this grid componenet i´ve developed. With this grid you can show the data like a normal table, remove the rows that you need, add rows, import data,...
5
by: jrod11 | last post by:
hi, I found a jquery html table sorting code i have implemented. I am trying to figure out how to edit how many colums there are, but every time i remove code that I think controls how many colums...
5
by: thatcollegeguy | last post by:
Below are my 3php and 2js files. I create a table using ajax/php and then want to change the values in the tables add(+ number for teamid) id's for each specific td in the table. I don't know...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.