from and to ranges in mysql

Question

Currently I have this kind of query
SELECT 
    part_number,
    part_name,
    attenuation_low_end,
    attenuation_high_end,
    optimum_fermentation_temp_f_low,
    optimum_fermentation_temp_f_high
FROM
    yeast_module 
WHERE 
    category = 3
AND
( 
    ( `attenuation_low_end` > '31' OR `attenuation_low_end` = '31' )
    
    AND 
    
    ( `attenuation_high_end` < '40' OR `attenuation_high_end` = '40' )
)

Where I'm trying to get the records with the range of low to high end from 31 and maximum of 40
But it returns me something like this

As you can notice it seems doesn't return the data between 31 to 40
Am I doing this right?
UPDATE

I'm expecting no return since, there's no data between 31-40

Barmar · Answer

You're comparing strings, which performs lexicographic comparisons rather than numeric comparisons. You need to convert to numbers. Adding 0 to a numeric string is a simple way to convert it to a number.
WHERE 0+attenuation_low_end >= 31 AND 0+attenuation_high_end <= 40

GMB · Answer

If you want ranges contained in the 31-40 range:

where attenuation_low_end >= 31 and attenuation_high_end <= 40

If you want ranges that overlap the 31-40 range:

where attenuation_low_end <= 40 and attenuation_high_end >= 31

If your data is of a string datatype, then you need to convert the values to integers so they can be compared as such.

Containment:

where attenuation_low_end + 0 >= 31 and attenuation_high_end + 0 <= 40

Overlap:

where attenuation_low_end + 0 <= 40 and attenuation_high_end + 0 >= 31

from and to ranges in mysql

2 Answers

Add your own answers!

Ask a Question