select with MD5 + CONCAT using where

Question

Where problem ? select work fine.. when check in where - give error Unknown column 'confirm' in 'where clause'

 SELECT 
    users_id, MD5(CONCAT(users_pass, users_email)) AS confirm
FROM
    (users_items) 
WHERE users_active = 0 
    AND confirm = '39a5eccb97f63a2bb649ddd95c5a3ba7' 
LIMIT 1 

Answer 1

because confirm is an alias.

You can't use an alias in the where clause in the same level of query.

You have to do

where xxx
and MD5(CONCAT(users_pass, users_email)) = '39a5eccb97f63a2bb649ddd95c5a3ba7'

Or, as stated by Vatev, you can use an alias in an HAVING clause. But use an HAVING clause may have an impact on performance.

Cause where filters your query (trying to use indexes, for example), and the HAVING clause is filtering on these filtered datas.

where xxx
HAVING confirm = '39a5eccb97f63a2bb649ddd95c5a3ba7'

Answer 2

SELECT 
    users_id,
    MD5(CONCAT(users_pass, users_email)) AS confirm
FROM
    (users_items) 
WHERE
    users_active = 0 
    AND MD5(CONCAT(users_pass, users_email)) = '39a5eccb97f63a2bb649ddd95c5a3ba7'
LIMIT 1

As for HAVING clause, AFAIK, it is used only with GROUP BY.

Added:

"In general, clauses used must be given in exactly the order shown in the syntax description. For example, a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause."

"Do not use HAVING for items that should be in the WHERE clause. For example, do not write the following:

SELECT col_name FROM tbl_name HAVING col_name > 0;

Write this instead:

SELECT col_name FROM tbl_name WHERE col_name > 0;"

These are quotations from http://dev.mysql.com/doc/refman/5.0/en/select.html.