question about aliases of subqueries

[arianhojat]

hello all, I want to do a query using an alias of a subquery value like so :

 

SELECT t1.evaluationID, t1.clientID
, (SELECT t2.employeeID FROM table2 t2 WHERE t2.somefield=t1.somefield) as evaluator
FROM table1 t1
WHERE evaluator=23

 

but i get an error that evaluator is an unknown column. is there anyway to do this without resulting to

 

SELECT t1.evaluationID, t1.clientID
, (SELECT t2.employeeID FROM table2 t2 WHERE t2.somefield=1) as evaluator
FROM table1 t1
WHERE (SELECT t2.employeeID FROM table2 t2 WHERE t2.somefield=1)=23 

which works...

 

Currently using mysql 4.1.12a-nt

 

Thanks for any help.

[fenway]

Try "HAVING evaluator = 23".

[arianhojat]

hey fenway,

Thought HAVING was only for GROUP BY statements?

[fenway]

hey fenway,

Thought HAVING was only for GROUP BY statements?

HAVING is for anything that gets evaluated after the "main" query... so in this case, it's possible that it's not aliased under after the main query has been executed, meaning that it's not visible to the WHERE clause "in time".