Join to tables link field between 2 values

Hi,

I have to tables which I need to join. First table has a unique value "Entry No", second table have 2 columns with "From Entry No." and "To Entry No" and a corresponding "Register Date". For each line in the first table I need to query the "Register Date" from second table where the Entry No. is in the Range "From... To".

Hope anyone can help me with this.

hi hope this helps

create data script

drop table #Table2
drop table #Table1

create table #Table1 (EntryNo int , PostingDate date , RegisteredDate date )
insert into #Table1 select 1050,'03-01-2023','01-01-2023'
insert into #Table1 select 1250,'01-01-2023','02-01-2023'

create table #Table2 (FromEntryNo int , ToEntryNo int , RegisteredDate date )
insert into #Table2 select 1000,1200,'01-01-2023'
insert into #Table2 select 1201,1300,'02-01-2023'

select 
          * 
from 
       #Table1, #Table2 
where 
       EntryNo >= FromEntryNo
              and 
       EntryNo <= ToEntryNo

2 Likes