Putting the 'HAVING' in there, works perfectly :)
> -----Original Message-----
> From: SHAWN L.GREEN [mailto:shawn.l.green@stripped]
> Sent: June 10, 2010 8:03 PM
> To: Steven Staples
> Cc: 'MySql'
> Subject: Re: WHERE clause from AS result
> 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
> > 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
> > 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
> Try this as an alternative:
> SELECT `email`, (SELECT CONCAT(`phone_pref`, '-', `phone_suff`) FROM
> WHERE `id`=`usertable`.`id`) AS pnum FROM `usertable` HAVING pnum LIKE
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.829 / Virus Database: 271.1.1/2917 - Release Date: 06/10/10