Click here to Skip to main content
15,893,486 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
Hi,

SQL
Create table table1(id1 int,id2 int,address1 varchar(100),address2 varchar(100))

insert into table1
values(1,3,'e','f'),(1,5,'i','j'),(1,6,'k','l')

create table table2(id1 int,id2 int,[address] varchar(100))

insert into table2(id1,id2)
values(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7)

select * from table1

select * from table2


Desired query result:
id1 id2 address
1   1   e
1   2   e
1   3   f
1   4   i
1   5   j
1   6   l
1   7   k


I would be very thankful if any one can help me with above output. Thanks in advance.

Thanks & Best Regards,
Sreeram.
Posted
Updated 16-Feb-15 5:08am
v2
Comments
PIEBALDconsult 16-Feb-15 11:06am    
Look up INNER JOIN.
Member 11155916 17-Feb-15 1:27am    
how to do with look up inner join ? please can you provide me the sample solution ?
Member 11155916 17-Feb-15 2:50am    
The below is table1

id1 id2 address1 address2
1 3 e f
1 5 i j
1 6 k l


The below is table 2

id1 id2 address
1 1 NULL
1 2 NULL
1 3 NULL
1 4 NULL
1 5 NULL
1 6 NULL
1 7 NULL

Now i want to update table 2 with table 1.

The output should be as below:
id1 id2 address
1 1 e
1 2 e
1 3 f
1 4 i
1 5 j
1 6 l
1 7 k
Kuthuparakkal 16-Feb-15 11:19am    
Homework ?
Member 11155916 17-Feb-15 1:28am    
what homework ? I didn't get you please ?

1 solution

use the below sql command after creating the table mentioned

SQL
select id1,id2,case when not exists (select id2  from table1 where id2=table2.id2) then isnull((select top 1  address1 from table1 where id2>table2.id2),(select top 1  address1 from table1 where id2<table2.id2 order by id2 desc)) else (select  address2  from table1 where id2=table2.id2) end as address from table2


this has been tested, please run the sql
 
Share this answer
 
v3
Comments
Abdulnazark 26-Feb-15 7:45am    
Just run the attached sql after creating the table from the above,you will get the result,

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900