Reputation: 13131
tableA contains {id, fromPos not null, toPos}
and has the following values
tableA (1, 5) // means any position greater than or equal to 5
tableA (2, 5, 10) // means any position between 5 and 10 (inclusive)
tableA (3, 6)
tableA (4, 7, 9)
Upvotes: 0
Views: 343
Reputation: 755114
For a given target value, X, the query appears to be:
SELECT id
FROM TableA
WHERE fromPos <= X
AND (toPos >= X OR toPos IS NULL);
Upvotes: 2
Reputation: 10941
select *
from tableA t
where t.fromPos <= requested_position
and coalesce(t.toPos, requested_position) >= requested_position
Coalesce
means that requested_position
will be put in comparison if t.toPos
appears to be null, thus, a comparison will always yield true and you'll process only t.fromPos <= requested_position
Or, you may use between
for better readability, which is the same:
select *
from tableA t
where requested_position between t.fromPos and coalesce(t.toPos, requested_position)
Upvotes: 3
Reputation: 107407
declare @position int
set @position = 8
select id from tablea
where @position >= fromPos
and (@position <= toPos or toPos is null)
create table tableA
(
id int not null,
fromPos int not null,
toPos int null
)
insert into dbo.tableA(id, fromPos) values (1, 5)
insert into dbo.tableA(id, fromPos, toPos) values (2, 5, 10)
insert into dbo.tableA(id, fromPos) values (3, 6)
insert into dbo.tableA(id, fromPos, toPos) values (4, 7, 9)
Upvotes: 2
Reputation: 212522
SELECT id
FROM <table>
WHERE <value> BETWEEN fromPos AND COALESCE(toPos,99999999)
Upvotes: 0
Reputation: 2467
Assuming that when there is no toPos mentioned in your example, it will be null.
1. Select * from tableA where fromPos <= 7 and (toPos=null or toPos >= 7)
2. Select * from tableA where fromPos <= 5 and (toPos=null or toPos >= 5)
3. Select * from tableA where fromPos <= 8 and (toPos=null or toPos >= 8)
Upvotes: 0