Where clause using alias column with IF and IFNULL

Question

I have an mysql query like below:

SELECT 
        sppt_ticket.*, 
        IF(sppt_read_support_ticket.ID_aks_user IS NULL,'N', 'Y') AS `read_status`, 
        IFNULL(readcomment.total_comment, 0) AS unread_comment
    FROM 
        sppt_ticket
    LEFT JOIN 
        sppt_read_support_ticket ON 
        sppt_ticket.ID_support_ticket = sppt_read_support_ticket.ID_support_ticket AND 
        ID_aks_user = 1
    LEFT JOIN
        (SELECT 
            sppt_comment.ID_support_ticket, SUM(IF(sppt_read_comment.ID_aks_user IS NULL, 1, 0)) 
    AS 
        total_comment
    FROM 
        sppt_comment
    LEFT JOIN 
        sppt_read_comment 
    ON 
        sppt_comment.ID_comment = sppt_read_comment.ID_comment 
    AND 
        sppt_read_comment.ID_aks_user = 1
    GROUP BY 
        sppt_comment.ID_support_ticket) AS readcomment ON readcomment.ID_support_ticket = sppt_ticket.ID_support_ticket

What I want to get in where clause is like this

WHERE read_status = 'Y'

I've tried using subquery, but still I didn't get it.. any help?

Answer 1

Have you tried this:

SELECT * FROM
(
-- your original query as a table
SELECT 
    sppt_ticket.*, 
    IF(sppt_read_support_ticket.ID_aks_user IS NULL,'N', 'Y') AS `read_status`, 
    IFNULL(readcomment.total_comment, 0) AS unread_comment
FROM 
    sppt_ticket
LEFT JOIN 
    sppt_read_support_ticket ON 
    sppt_ticket.ID_support_ticket = sppt_read_support_ticket.ID_support_ticket AND 
    ID_aks_user = 1
LEFT JOIN
    (SELECT 
        sppt_comment.ID_support_ticket, SUM(IF(sppt_read_comment.ID_aks_user IS NULL, 1, 0)) 
AS 
    total_comment
FROM 
    sppt_comment
LEFT JOIN 
    sppt_read_comment 
ON 
    sppt_comment.ID_comment = sppt_read_comment.ID_comment 
AND 
    sppt_read_comment.ID_aks_user = 1
GROUP BY 
    sppt_comment.ID_support_ticket) AS readcomment ON readcomment.ID_support_ticket = sppt_ticket.ID_support_ticket
)
as temptable
where read_status = 'Y' -- this should work

Or you can use HAVING instead of WHERE if you do not want to treat your query as a table:

SELECT 
    sppt_ticket.*, 
    IF(sppt_read_support_ticket.ID_aks_user IS NULL,'N', 'Y') AS `read_status`, 
    IFNULL(readcomment.total_comment, 0) AS unread_comment
FROM 
    sppt_ticket
LEFT JOIN 
    sppt_read_support_ticket ON 
    sppt_ticket.ID_support_ticket = sppt_read_support_ticket.ID_support_ticket AND 
    ID_aks_user = 1
LEFT JOIN
    (SELECT 
        sppt_comment.ID_support_ticket, SUM(IF(sppt_read_comment.ID_aks_user IS NULL, 1, 0)) 
AS 
    total_comment
FROM 
    sppt_comment
LEFT JOIN 
    sppt_read_comment 
ON 
    sppt_comment.ID_comment = sppt_read_comment.ID_comment 
AND 
    sppt_read_comment.ID_aks_user = 1
GROUP BY 
    sppt_comment.ID_support_ticket) AS readcomment ON readcomment.ID_support_ticket = sppt_ticket.ID_support_ticket
HAVING read_status = 'Y' -- use HAVING instead of WHERE

The reason why treating your original query as a table works is because of the way values are evaluated in the query. In your original query, the alias read_status cannot be used with the WHERE clause because the actual value might not yet be known when the WHERE clause is evaluated. As documented in Section B.1.5.4, “Problems with Column Aliases”. Treating it as a table ensures that the value for read_status has already been evaluated.

For the HAVING approach, MySQL created an extension to standard SQL that permits references in the HAVING clause to aliased expressions in the select list.