Locke Locke - 5 days ago 5
SQL Question

Selecting from a range based on multiple range criteria

I have an interesting quandary here.

Imagine I have a list of numbers as follows: 2, 4, 9. Let's call them

foo
.

Now imagine I have the following data:

| Id | ForeignKey | RangeStart | RangeEnd |
|----|------------|------------|----------|
| 1 | 1 | 1 | 3 |
| 2 | 1 | 4 | 5 |
| 3 | 1 | 6 | 9 |
| 4 | 2 | 1 | 2 |
| 5 | 2 | 3 | 3 |
| 6 | 2 | 4 | 9 |
| 7 | 3 | 1 | 5 |
| 8 | 3 | 6 | 9 |


I need to find any rows where any of the items from
foo
fall in between
RangeStart
and
RangeEnd
. In this example, Row Id 5 would not be included in the result set, but all other rows would.

It's an easy enough problem to solve in my controller (just filter out the results in a loop), but I'm wondering if there isn't a set-based solution to this problem.

Answer

SQL DEMO

SELECT DISTINCT Table1.*
FROM table1
JOIN foo
  ON foo.value between `RangeStart` and `RangeEnd`

enter image description here OUTPUT

Comments