I want to take the value written in the number field in table A and find which range it corresponds to in the high range and low range fields in table B and show it as in the result table. If in more than one range, it should take whichever comes first (B_id is smaller)

A table

for example( number = 10)

B table

for example( Low range >10 and high range <10 the result B_id = 1)

Result Table

CodePudding user response：

You can try the following SQL.

The BETWEEN used to find the ranges and ROW_NUMBER helps to get the smaller value.

create table tableA(a_id int, Number int)
insert into tableA values(1,10)
insert into tableA values(2,50)
insert into tableA values(3,60)
insert into tableA values(4,52)

create table tableB(b_id int, lowRange int, highRange int, Rtype varchar(10))
insert into tableB values (1,5,30,'ACARD')
insert into tableB values (2,35,55,'BCARD')
insert into tableB values (3,50,110,'CCARD')

;with CTE AS(
select a.a_id Id, a.Number, b.Rtype, ROW_NUMBER() OVER(PARTITION BY a.a_id ORDER BY LowRange) RN
from tableA A
inner join tableB B on A.Number between B.lowRange and B.highRange
)
select Id, Number, Rtype 
from CTE
where RN = 1

drop table tableA
drop table tableB

Output:

Id          Number      Rtype
----------- ----------- ----------
1           10          ACARD
2           50          BCARD
3           60          CCARD
4           52          BCARD