Mysql: Selecting values between two columns

Question

I'm trying to select a value between 2 columns. Here is my dataset

id    from    to    price
1     0.00    2.00  2.50
2     2.00    3.00  3.00
3     3.00    4.00  4.50

My goal, if I have a value of 2 is to select the line with the ID 1 (between from and to). So here is the query I'm using :

select * from table where 2 between from and to;

And here are the results that MySQL returns when executing this query :

id    from    to    price
1     0.00    2.00  2.50
2     2.00    3.00  3.00

And the result I'm looking for is the following :

id    from    to    price
1     0.00    2.00  2.50

I've tried using < and >, etc. But, I'm always getting two results. Any help would be much appreciated.

If your value is `2`, then the query matches it against the column `to` or `id`? — hjpotter92, Oct 10 '12 at 17:24
*My goal, if I have a value of 2 is to select the line with the ID 2* Your sample result has `ID=1`, which is it? — D'Arcy Rittich, Oct 10 '12 at 17:26

score 35 · Answer 1 · answered Jul 26 '14 at 14:40

35

You could try this:

SELECT * FROM `table` WHERE 2 BETWEEN `from` AND `to`

answered Jul 26 '14 at 14:40

Juliano Lima

3

I don't understand why this is getting down votes, it's a perfectly valid answer! – tftd Aug 24 '15 at 16:08
1

@tftd, it is getting down-voted because it is basically the same query that was already placed in the question. Also, this SQL query does not do what the question author asked to do. – Tim Groeneveld Feb 03 '16 at 04:12

Vikdor · Accepted Answer · 2012-10-10T17:35:38.457

14

SO, you don't want the lower bound to be inclusive, right?

SET @value = 2;
SELECT * FROM table WHERE from > @value AND @value <= to;

edited Oct 10 '12 at 17:35

answered Oct 10 '12 at 17:23

Vikdor

I will tell you in plain english (easier). If my value is between from and to (inclusively for the column to), then I select the price. So, If I have a value of 2, the result must be the Id 1. If I have 2.001, the value must be Id 2. – Oct 10 '12 at 17:27
@nblmedia but your data has a little problem: the lower boundary of record 2 is *exactly* equal to the upper boundary of record 1, so both intervals are included (I mean, 2 is both an element of [0,2] and of [2,3]). So, either define an open condition, like the one Vikdor proposes, or define intervals for which interceptions are really empty – Barranka Oct 10 '12 at 17:38

D'Arcy Rittich · Answer 3 · 2012-10-10T17:39:01.883

9

Query 1:

select * 
from `table` 
where `from` < 2 and `to` >= 2

Output:

| ID | FROM | TO | PRICE |
--------------------------
|  1 |    0 |  2 |     3 |

Query 2:

select * 
from `table` 
where `from` < 2.001 and `to` >= 2.001

Output:

| ID | FROM | TO | PRICE |
--------------------------
|  2 |    2 |  3 |     3 |

Note: With this approach, you will get no rows back for value 0, unless you modify the query to accommodate that.

edited Oct 10 '12 at 17:39

answered Oct 10 '12 at 17:33

D'Arcy Rittich

Your first answer was correct : select * from table where 2 > from and 2 <= to. It works for the other values. – Oct 10 '12 at 17:37
@nblmedia My two queries are identical. I just provided the output for your two data examples, `2` and `2.001`. – D'Arcy Rittich Oct 10 '12 at 17:47

score 1 · Answer 4 · edited Dec 21 '12 at 05:48

1

You can also try this ,

select * from table where (from-to) = 2  // if you want the exact distance to be 2 
select * from table where (from-to) >= 2 // Distance more than 2

edited Dec 21 '12 at 05:48

bipen

answered Dec 21 '12 at 05:27

user1859658

4 Answers4