table1 having nulls then fetch from table2 ?

[venkat6134]

Hi all,

I have two tables with columns as;

in table1;

empno,ename,address1,address2,address3,city,state

in table2;

empno,ename,address11,address22,address33,city,state

Now i want to write a query as,

to display the table1 data with full address,
if there is null values present in table1 in address columns (i.e all there address1,address2,address3 columns are null) , i want to fetch that row address (from address11,address22,address33 columns) from table2 for that row based on the empno and display at a time.

Please give me an idea to resolve this problem soon.

Thanking you.

[SpacePhoenix]

Why are the addresses in different tables when they are presumably about the same entity (presumably employees)?

[r937]

why did you repost this question? i answered it yesterday

http://www.sitepoint.com/forums/show...values-present

[venkat6134]

why did you repost this question? i answered it yesterday

http://www.sitepoint.com/forums/show...values-present

Thank you for your reply.

But that COALESCE function is checking one by one column.
i want to check if all the 3 columns are null (but not any one or two), then only i want to go for second-table address columns?

[r937]

use COALESCE on the concatenation of the three columns...

SELECT COALESCE(address1||address2||address3, address11) AS address1
     , COALESCE(address1||address2||address3, address22) AS address2
     , COALESCE(address1||address2||address3, address33) AS address3
...