Hi Rick, ----- Original Message ----- From: "Rick Emery" <[EMAIL PROTECTED]>
> Description: > The SELECT statement fails to recognize that an alias (mycol) was set for a > column. Note the column name is created from a table alias (c1) > > mysql> select c1.id as mycol from colors c1 right join colors c2 on > c1.color=c2.color AND c1.id != c2.id where mycol is not null; > > ERROR 1054: Unknown column 'mycol' in 'where clause' This is actually correct and documented behaviour, see: http://www.mysql.com/doc/P/r/Problems_with_alias.html Quoted from there: [snip] ANSI SQL doesn't allow you to refer to an alias in a WHERE clause. This is because when the WHERE code is executed the column value may not yet be determined. For example, the following query is illegal: SELECT id,COUNT(*) AS cnt FROM table_name WHERE cnt > 0 GROUP BY id; [snap] I have now put an explicit reference to this section in the explanation of the SELECT statement in the manual, as I'm sure you're not the first to trip over this one ;-) Thanks for your feedback! Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
