query value in range of two fields-CodePudding

I have a table `

table VALUES

from UNSIGNED INT 

to UNSIGNED INT

value VARCHAR(2) `

example of filling

0,100000,good
100001,200000,better
200001,300000,best
---

etc.

I need to query a value by number, e.g. 100 (is between 0 and 100000), should return good 199000 should return better, 200002 should return best

What SQL syntax should/can I use?

I'm trying rn to fill the table with exact values from the range, but the process takes too long, there are more then 200k of big ranges.

CodePudding user response：

I found a solution, I had to use HAVING statement

select * from VALUES having 100 >= `from` and 100 <= `to`;

CodePudding user response：

there. I found a solution and the statement like as:

update VALUES set result = "good" where value between 0 and 100000

update VALUES set result = "better" where value between 100001 and 200000

update VALUES set result = "best" where value between 200001 and 300000

CodePudding user response：

It sunds le qa simple WHERE clause should be sufficient.

CREATE PROCEDURE lookup_range(ni INT)
BEGIN
    SELECT value FROM VALUES
    WHERE ni >= from and ni <= to;
END;

You can then call it using:

CALL lookup_value(100);
CALL lookup_value(199000);