List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:August 3 1999 12:04pm
Subject:Re: "where" doesn't work, but "having" works...
View as plain text  
Hi Faisal

Faisal Nasim wrote:
> 
> mysql> select concat("cool","kid") as whiz where whiz like "%cool%";
> ERROR 1064: parse error near 'where whiz like "%cool%"' at line 1

The syntax for an select is:
SELECT ... FROM ... WHERE ...

In the above you don't specify the FROM part, so this can't work :)

> mysql> select concat("cool","kid") as whiz;
> +---------+
> | whiz    |
> +---------+
> | coolkid |
> +---------+
> 1 row in set (0.00 sec)
> 
> Why doesn't the first one work? That is just an example as I know
> its stupid to give two constants and search for a word in it :)
> 
> EXAMPLE #2:
> 
> mysql> select *,concat(member,email) as whiz from member;
> 
> Works fine! And there is a "whiz" column which has the value of member
> and email.
> 
> But,
> 
> mysql> select *,concat(member,email) as whiz from member where whiz like "%f%";
> ERROR 1054: Unknown column 'whiz' in 'where clause'
> 
> Why?

You can't use alias values in the WHERE part.

> But,
> 
> mysql> select *,concat(member,email) as whiz from member having whiz like "%f%";
> 
> Seems to do the job....
> 
> why not "where"?
> 
> P.S> Please CC to my mailbox as I am not on the list! :P
> 
> Thanx.

The difference of WHERE and HAVING is the time it is used.
The WHERE part is used when getting the rows out of the tables.
The HAVING part is used, after the rows are pulled out.
So the HAVING part is used on the results of the WHERE part.
The aliases are created while the WHERE part is processed, so it doesn't exist before the
WHERE part has ended.
To use aliases and GROUP-functions to restrict the output the HAVING syntax was
introduced, so this is the correct and only way to go.

Tschau
Christian

Thread
"where" doesn't work, but "having" works...Faisal Nasim30 Jul
  • Re: "where" doesn't work, but "having" works...Christian Mack3 Aug