using conditional statements in where-clause

[pwiegers]

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

[Captain Paralytic]

On 30 Jan, 13:56, "[e-mail]" <[e-mail]:

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

[pwiegers]

>

I would have expected it to be
from users u having age<50

Me too.. but that does not work :-(
Any more idea's?

[Curtis]

On Tue, 30 Jan 2007 12:56:13 -0800, pw******@gmail.com
<pw******@gmail.comwrote:

>>
I would have expected it to be
from users u having age<50

Me too.. but that does not work :-(
Any more idea's?

You'd probably get more responses from a SQL newsgroup. This doesn't seem
PHP-specific.

--
Curtis, http://dyersweb.com

[Tim Roberts]

"Captain Paralytic" <pa**********@yahoo.comwrote:

>

On 30 Jan, 13:56, "[e-mail]" <[e-mail]:

>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, ti**@probo.com
Providenza & Boekelheide, Inc.

[Captain Paralytic]

On 2 Feb, 06:40, Tim Roberts <[e-mail]:

"Captain Paralytic" <[e-mail]:

On 30 Jan, 13:56, "[e-mail]" <[e-mail]:

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, [e-mail]
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."