CODEWITHSUNDEEP
mysqlsql
Sohel Mansuri
Sohel Mansuri

Reputation: 564

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

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'?

Upvotes: 3

Views: 159

Answers (2)

maček
maček

Reputation: 77778

I believe you can do something like this

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

FROM users_codes
HAVING Expired = 'no';

Upvotes: 2

John Woo
John Woo

Reputation: 263703

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'

Upvotes: 7

Related Questions