RE: WHERE clause from AS result
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
Re: WHERE clause from AS result
You can use an Alias in ORDER BY but not in WHERE clauses. Keith On Thu, 2010-06-10 at 16:38 -0400, 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' Any ideas? Steven Staples Keith J. Clark Business ManagerOwner The BookwormWaterloo Hosting Quality Used Books Complete Web Hosting Provider www.k-wbookworm.com www.waterloohosting.com sa...@k-wbookworm.com sa...@waterloohosting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
WHERE clause from AS result
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' Any ideas? Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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%'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
