Quite a bizarre scenario. 1st thing 1st, best way to share your sample data is to share it in a proper manner it will help us to recognize the data.
Something like this :
Declare @Table1 table (
id1 int,
id2 int,
address1 varchar(20),
address2 varchar(20)
)
insert into @Table1
select 1, 3, 'e','f' union all
select 1, 5, 'i','j' union all
select 1, 6, 'k','l'
Declare @Table2 table (
id1 int,
id2 int,
address1 varchar(20)
)
insert into @Table2
select 1, 1,'NULL' union all
select 1, 2,'NULL' union all
select 1, 3,'NULL' union all
select 1, 4,'NULL' union all
select 1, 5,'NULL' union all
select 1, 6,'NULL' union all
select 1, 7,'NULL';
you should do the same practice for desired output. Anyways let go for the solution. Query will working on SQL SERVER 2012 and above version.
Note: this solution is solely based on sample data
Following is the solution:
Select t2.id1, t2.id2, case when t1.id2 = t2.id2 then t1.address2 else t1.address1 end
from
(
Select M.id1, M.id2, M.address1, M.address2, case when previd is null then m.id1 else previd + 1 end as startID
,case when M.id2 = lastId and M.id2 < rng.maxid2 then rng.maxid2 else M.id2 end as endID
from (
Select t1.id1, t1.id2, t1.address1, t1.address2
, LAG(t1.id2 , 1) OVER (Partition by t1.id1 order by t1.id1 ) as previd
, LAST_VALUE(t1.id2)OVER (Partition by t1.id1 order by t1.id1 ) as lastId
from @Table1 t1
) M
join
(
Select t2.id1, max(t2.id2) AS maxid2
from @Table2 t2
Group by t2.id1
) rng on m.id1 = rng.id1
) t1
join @Table2 t2 on t1.id1 = t2.id1 and t2.id2 between t1.startID and t1.endID
hope it helps.