Mysql – Select rows based on condition for decimal columns returns nothing

This realy doesn’t make sense, but just to point to some of the issues I’ll answer the question with code that works with the data provided. Hope the issues will be clear and that years and months as a decimal number will be escaped one way or another – for good!

How to make it work and return row with id = 10?

--      S a m p l e    D a t a :
CREATE TABLE `ages` (
    `id` tinyint(3) UNSIGNED NOT NULL,
    `min_age` decimal(4,2) NOT NULL,
    `max_age` decimal(4,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `ages` (`id`, `min_age`, `max_age`) VALUES
(1, 4.00, 4.30),
(2, 4.40, 4.70),
(3, 4.80, 4.11),
(4, 5.00, 5.50),
(5, 5.60, 5.11),
(6, 6.00, 6.50),
(7, 6.60, 6.11),
(8, 7.00, 7.50),
(9, 7.60, 7.11),
(10, 8.00, 8.11),
(11, 9.00, 9.11),
(12, 10.00, 10.11),
(13, 11.00, 11.11),
(14, 12.00, 12.11),
(15, 13.00, 13.11),
(16, 14.00, 14.11),
(17, 15.00, 24.11);

… with this sample data you tryed to select the row with id = 10 using this Where clause :

...
WHERE `min_age` <= 8.6 AND `max_age` >= 8.6;
--  and you got no rows selected ...
--
-- this condition could fetch just one (1) row, 
-- the row where both min_age and max_age have the same decimal value of 8.6

… where 8.6 is 8 years and 6 months – if that is so you should extract years and months out of both min_age and max_age columns – for that you could (with an exception) create a cte like this …

WITH
   yrs_mths AS
     ( Select   id, 
                min_age, Cast(min_age_yr as UNSIGNED) as min_age_yr, 
                Cast(Case When SubStr(min_age_mth, -1) = '0' 
                          Then SubStr(min_age_mth, 1, 1) 
                     Else min_age_mth 
                     End as UNSIGNED) as min_age_mth, 
                --
                max_age, Cast(max_age_yr as UNSIGNED) as max_age_yr,
                Cast(Case When SubStr(max_age_mth, -1) = '0' 
                          Then SubStr(max_age_mth, 1, 1) 
                     Else max_age_mth 
                     End as UNSIGNED) as max_age_mth
       From     ( Select  id, min_age, max_age,
                          SubStr(min_age, 1, InStr(min_age, '.') - 1) as min_age_yr, 
                          SubStr(min_age, InStr(min_age, '.') + 1) as min_age_mth, 
                          SubStr(max_age, 1, InStr(max_age, '.') - 1) as max_age_yr, 
                          SubStr(max_age, InStr(max_age, '.') + 1) as max_age_mth
                  From ages ) y
     )

… now you have nice and shiny integer values for years and months – BUT there is exception (mentioned in comments) – if you can make distinction between e.g. 6 year and 1 month vs 6 years and 10 months written as a decimal in the database then you handle that too in the code above cause it doesn’t cope (with current syntax) with this kind of exception …

… let’s forget the exception and see how to use the above code to select the row with id = 10 using 8.6 decimal presented as years.months …

--    M a i n    S Q L : 
SELECT    id, min_age, max_age  
FROM      yrs_mths
WHERE     ( min_age_yr < 8 OR (min_age_yr = 8 And min_age_mth <= 6) ) AND 
          ( max_age_yr > 8 OR (max_age_yr = 8 And max_age_mth >= 6) );

… if your 8.6 is 8 years and 6 months then you’ll need to use OR operator twice to filter the sample data by logic described …

/*    R e s u l t :
id  min_age max_age
-- -------- -------
10     8.00    8.11    */

See the fiddle here.