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=gcdmg-mysql-2@m.gmane.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 Manager Owner
The Bookworm Waterloo Hosting
Quality Used Books Complete Web Hosting Provider
www.k-wbookworm.com www.waterloohosting.com
sales@k-wbookworm.com sales@waterloohosting.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.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=gcdmg-mysql-2@m.gmane.org

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.green@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=gcdmg-mysql-2@m.gmane.org