Query Master Query Master - 6 months ago 14
MySQL Question

I want some null result with the help of this Query

Query

SELECT
CASE
WHEN `tomato_rating` = 0 THEN 'NULL'
WHEN `tomato_rating` != 0 THEN `tomato_rating`
END `tomato_rating`,
ur.`user_rating`,
ABS(tomato_rating - ur.`user_rating`) AS avg_guess_error
FROM user_rating ur
LEFT JOIN videos v
ON ur.vid_id = v.id
WHERE ur.user_id = '1'


See the result screenshot for verification

enter image description here

Result Required

NULL - 50 NULL
50 - 30 20
65 - 33 32
NULL - 16 NULL

Answer

I think this is what you're looking for:

SELECT
  IF (`tomato_rating` = 0, 'NULL', `tomato_rating`) as `tomato_rating`,
  ur.`user_rating`,
  IF (`tomato_rating` = 0, 'NULL', ABS(tomato_rating - ur.`user_rating`))
    as avg_guess_error  
FROM user_rating ur
LEFT JOIN videos v ON ur.vid_id = v.id
WHERE ur.user_id = '1'

Notice that you're using string 'NULL' instead of NULL.

Comments