CODEWITHSUNDEEP
mysqlsqlstringwhere-clause
Pablo
Pablo

Reputation: 1435

from and to ranges in mysql

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 enter image description here

As you can notice it seems doesn't return the data between 31 to 40

Am I doing this right?

UPDATE

enter image description here

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

Upvotes: 0

Views: 83

Answers (2)

GMB
GMB

Reputation: 222502

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

Upvotes: 1

Barmar
Barmar

Reputation: 781255

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

Upvotes: 1

Related Questions