CODEWITHSUNDEEP
sqlsql-server
Jojo Kurniawan
Jojo Kurniawan

Reputation: 25

SQL: Checking if a number within range of multiple ranges

Lets say we have 2 tables.

Table Values
Id   Group    Value
--------------------
A     X        15  
B     Y        55

Table Ranges
Group   LowLimit     HighLimit
--------------------------------
X         0             10       
X         20            30 
Y         30            40 
Y         50            60

I would like to check which Values.Id has value that falls outside the LowLimit and HighLimit of the group it belongs to in Ranges (notice each group has multiple ranges).

The desired result would be

Table Result
Id
--------------
A    (because the value 15 is outside the range of both (0..10) and (20..30) 
while B is not part of the result because it is within the range of (50..60) even though it is outside the range of (30..40))

What would the sql would be like?

Upvotes: 0

Views: 2554

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

One option uses an exists query:

SELECT
    v.ID
FROM [Values] v
WHERE NOT EXISTS (SELECT 1 FROM [Ranges] r
                  WHERE v.[Group] = r.[Group] AND
                        v.[Value] BETWEEN r.[LowLimit] AND r.[HighLimit]);

Demo

In plain English, this will check each ID row from Values and check it there does not exist even one range which contains the value. If no such range exists, then this non matching ID would be reported.

Upvotes: 4

Related Questions