List:General Discussion« Previous MessageNext Message »
From:Steven Staples Date:June 11 2010 1:37pm
Subject:RE: WHERE clause from AS result
View as plain text  
Putting the 'HAVING' in there, works perfectly :)

THANKS!


Steven Staples



> -----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
> 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

Thread
WHERE clause from AS resultSteven Staples10 Jun
  • Re: WHERE clause from AS resultKeith Clark11 Jun
  • Re: WHERE clause from AS resultSHAWN L.GREEN11 Jun
    • RE: WHERE clause from AS resultSteven Staples11 Jun