How to select using a WHERE condition on an aliased column using MySQL?

Question

I have the following Query:

SELECT IF(TIMESTAMPDIFF(HOUR,users_codes.timestam,CURRENT_TIMESTAMP()) < 48,'no','yes') AS Expired
FROM users_codes

How do I return only rows that have 'no'?

Answer 1

add a WHERE condition. You cannot use the ALIAS on the where clause.

SELECT IF(TIMESTAMPDIFF(HOUR,users_codes.timestam,CURRENT_TIMESTAMP()) < 48,'no','yes') AS Expired 
FROM   users_codes
WHERE  IF(TIMESTAMPDIFF(HOUR,users_codes.timestam,CURRENT_TIMESTAMP()) < 48,'no','yes') = 'no'

or use a subquery

SELECT *
FROM
(
    SELECT IF(TIMESTAMPDIFF(HOUR,users_codes.timestam,CURRENT_TIMESTAMP()) < 48,'no','yes') AS Expired 
    FROM   users_codes
) s
WHERE  Expired  = 'no'