Posted by Captain Paralytic on 02/02/07 10:33

On 2 Feb, 06:40, Tim Roberts <t...@probo.com> wrote:
> "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
>
> >On 30 Jan, 13:56, "pwieg...@gmail.com" <pwieg...@gmail.com> wrote:
> >> Hi,
>
> >> I'm trying to use the result of a conditional statement in a where
> >> clause, but i'm getting 1)nowhere 2) desperate :-)
>
> >> The query is simple:
> >> --------
> >> SELECT
> >> idUser,
> >> (@ageraw:=YEAR(CURRENT_DATE()) - YEAR(dateofbirth) -
> >> (RIGHT(CURRENT_DATE(),5)<'12-31')) AS temp,
> >> @age:=if( @ageraw > 100,0,
> >> if( @ageraw < 10,0 ,
> >> @ageraw
> >> )) as age
> >> from users u
> >> ---------
>
> >> this works as a charm. But now I want to select users of a certain
> >> age, and I add a where clause:
> >> from users u having @age<50
>
> >> This is not working. Nor is any other variant I can think of. How can
> >> this be achieved?
>
> >> greeting, and many thanks,
>
> >> Paul
>
> >I would have expected it to be
> >from users u having age<50
>
> Ummm, why isn't it "FROM users u WHERE age<50"? Do you have a GROUP BY
> clause that you didn't show us? HAVING is only used with GROUP BY.
> --
> Tim Roberts, t...@probo.com
> Providenza & Boekelheide, Inc.- Hide quoted text -
>
> - Show quoted text -

Because you can't use column aliases in the WHERE clause but you can
in the HAVING clause.

>From the MySQL manual:
"A similar MySQL extension applies to the HAVING clause. The SQL
standard does not allow the HAVING clause to name any column that is
not found in the GROUP BY clause if it is not enclosed in an aggregate
function. MySQL allows the use of such columns to simplify
calculations."

[Back to original message]