SQL: Checking if a number within range of multiple ranges

Question

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?

use NOT EXISTS . If you are not familiar with this, refer to w3schools.com/sql/sql_exists.asp — Squirrel
– Squirrel, Commented Jul 19, 2018 at 2:02

Tim Biegeleisen · Accepted Answer · 2018-07-19 02:08:58Z

4

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.

edited Jul 19, 2018 at 2:08

answered Jul 19, 2018 at 2:03

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL: Checking if a number within range of multiple ranges

1 Answer 1

Demo

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related