TransWikia.com

from and to ranges in mysql

Stack Overflow Asked by Rae Ian on January 8, 2021

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

2 Answers

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

Answered by Barmar on January 8, 2021

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

Answered by GMB on January 8, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP