sqlmysqlif-statement

How to save the result of a condition (IF) into a variable?

Is there any way I can avoid duplicating the "if"?

SELECT 
    a.a_id,
    IF(acd.dt_changed IS NULL, a.dt, acd.dt_changed) AS dt
FROM 
    articles a
LEFT JOIN 
    articles_changed_dt acd 
ON 
    a.a_id = acd.a_id
WHERE 
    a.status = 1 
    AND IF(acd.dt_changed IS NULL, a.dt, acd.dt_changed) 
    BETWEEN '2025-02-06 00:00:00' AND '2025-02-10 23:59:59';
Solution

  • Use COALESCE() with HAVING:

    SELECT 
    a.a_id,
    COALESCE(acd.dt_changed, a.dt) AS dt
FROM articles a
LEFT JOIN articles_changed_dt acd 
    ON a.a_id = acd.a_id
WHERE 
    a.status = 1
HAVING
    dt BETWEEN '2025-02-06 00:00:00' AND '2025-02-10 23:59:59';

    Note that MySQL has overloaded the HAVING operator such that it accepts aliases.