On 6/10/2010 4:38 PM, Steven Staples wrote:
Ok, I have done it before, where I have used the AS result in an ORDER BY,
but now, I can't figure out why I can't use it in a WHERE clause?
SELECT `email`, (SELECT CONCAT(`phone_pref`, '-', `phone_suff`) FROM `pnums`
WHERE `id`=`usertable`.`id`) AS pnum FROM `usertable` WHERE pnum LIKE
'555-12%';
It gives me this error:
Error Code : 1054
Unknown column 'pnum' in 'where clause'
It has to do with the order in which things happen in the query. The
results of the subquery are computed in the FROM...WHERE... part of the
query. There is no way that the results could be named so that the WHERE
clause could handle them. This is why aliases are available for use in
the clauses processed after the WHERE clause - the GROUP BY and HAVING
clauses.
Try this as an alternative:
SELECT `email`, (SELECT CONCAT(`phone_pref`, '-', `phone_suff`) FROM `pnums`
WHERE `id`=`usertable`.`id`) AS pnum FROM `usertable` HAVING pnum LIKE
'555-12%';
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
