Putting the 'HAVING' in there, works perfectly :) THANKS!
Steven Staples > -----Original Message----- > From: SHAWN L.GREEN [mailto:shawn.l.gr...@oracle.com] > 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 > 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%'; > 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 > 02:35:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
