MySQL Query Between Two Ranges

Question

I need help with a query. I am taking input from a user where they enter a range between 1-100. So it could be like 30-40 or 66-99. Then I need a query to pull data from a table that has a high_range and a low_range to find a match to any number in their range.

So if a user did 30-40 and the table had entries for 1-80, 21-33, 32-40, 40-41, 66-99, and 1-29 it would find all but the last two in the table.

What is the easiest why to do this?

Thanks

@Farkie Looks like you have the correct answer. SELECT * FROM table WHERE (high_range between 30 AND 40 OR low_range between 30 and 40) — T3chn0crat
– T3chn0crat, Commented Jun 27, 2014 at 16:04

jcaron · Accepted Answer · 2014-06-26 22:34:56Z

3

If I understood correctly (i.e. you want any range that overlaps the one entered by the user), I'd say:

SELECT * FROM table WHERE low <= $high AND high >= $low

answered Jun 26, 2014 at 22:34

jcaron

17.8k6 gold badges36 silver badges47 bronze badges

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

4 Comments

jcaron Over a year ago

Not quite sure why my answer got down voted, I believe it does exactly what the OP asked for. Of course low and high are the table columns and $high and $low are the user inputs.

Rahul Over a year ago

I didn't downvoted but I think we can just use BETWEEN operator since OP wants data inclusive but anyways your answer is correct.

jcaron Over a year ago

@Rahul, BETWEEN works if you compare 3 values (i.e., one value against one range). Here we have 4 to compare (2 ranges).

T3chn0crat Over a year ago

I thought I had tried your solution and it didn't work, but I guess I was mistaken. So far it's pulling the correct entries.

Zack · Accepted Answer · 2014-06-26 22:56:35Z

0

What I understood is that the range is stored in this format low-high. If that is the case, then this is a poor design. I suggest splitting the values into two columns: low, and high.

If you already have the values split, you can use some statement like:

SELECT * FROM myTable WHERE low <= $needleHigherBound AND high >= $needleLowerBound

If you have the values stored in one column, and insist they stay so, You might find the SUBSTRING_INDEX function of MySQL useful. But in this case, you'll have to write a complicated query to parse all the values of all the rows, and then compare them to your search values. It seems like a lot of effort to cover up a design flaw.

answered Jun 26, 2014 at 22:56

Zack

1891 gold badge4 silver badges18 bronze badges

Collectives™ on Stack Overflow

MySQL Query Between Two Ranges

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related