How I can get limit_exceded value if value is less than or greater than in "limit_exceded" column i would get 1 else 0 in mysql

user_7566744 0
Viewed 50

database table_img for better understanding Pardon me for weak English writing

my sql query that i am getting value,low_value,high_value and limit_exceded_. issue is in limit_exceded to not get correct values, it shows all 0.

SELECT `myvalues`.`value`, `sub_types`.`low_value`, `sub_types`.`high_value`,               
 (case when myvalues.value > sub_types.low_value  and myvalues.value < sub_types.high_value then 1 else 0 end) as limit_exceded
FROM `myvalues`
JOIN `sub_types` ON `myvalues`.`sub_type_id` = `sub_types`.`id`
WHERE `myvalues`.`sub_type_id` IN('68')
AND `myvalues`.`observation_id` IN('455', '471', '470', '469', '468', '467', '466', '465', '462', '461', '460', '459', '458', '457', '456', '372', '453', '373', '376', '439', '440', '441', '442', '443', '445', '446', '447', '448', '452', '454')

I want to get int 1 in front of those values whose value is less than 40 or greater than 180. Also it would it be appreciated if extract max and min from this list and count limit_exceded values

expected result is set value 1 where values is 900,9 and 1 etc

mysql sql
1 Answers

issue is in limit_exceded to not get correct values, it shows all 0

I suspect the issue is the types. The way the data lines up in the image and the use of strings in the WHERE clause suggest that the values are strings not numbers. A simply way to convert is to use + 0:

SELECT v.value, st.low_value, st.high_value,               
       (v.value + 0) > (st.low_value + 0)  and (v.value + 0) < (st.high_value + 0) as limit_exceded
FROM myvalues v JOIN
     sub_types st
     ON v.sub_type_id = st.id
WHERE v.sub_type_id IN ('68') AND
      v.observation_id IN ('455', '471', '470', '469', '468', '467', '466', '465', '462', '461', '460', '459', '458', '457', '456', '372', '453', '373', '376', '439', '440', '441', '442', '443', '445', '446', '447', '448', '452', '454')

Notes:

  • Table aliases make the query easier to write and to read.
  • Unnecessary backticks just make the query harder to write and to read.
  • In MySQL, you don't need the CASE expression -- you can just use the boolean expression.

That said, you should fix the data, not the query. If the values are numbers, store them as numbers, not string.

user_11440350
Related