using conditional statements in where-clause | | |
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
| | | | re: using conditional statements in where-clause
On 30 Jan, 13:56, "pwieg...@gmail.com" <pwieg...@gmail.comwrote: Quote:
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 | | | | re: using conditional statements in where-clause
> Quote:
I would have expected it to be
from users u having age<50
Me too.. but that does not work :-(
Any more idea's? | | | | re: using conditional statements in where-clause
On Tue, 30 Jan 2007 12:56:13 -0800, pwiegers@gmail.com
<pwiegers@gmail.comwrote: Quote: Quote:
>>
>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 | | | | re: using conditional statements in where-clause
"Captain Paralytic" <paul_lautman@yahoo.comwrote: Quote:
>
>
>On 30 Jan, 13:56, "pwieg...@gmail.com" <pwieg...@gmail.comwrote: Quote:
>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, timr@probo.com
Providenza & Boekelheide, Inc. | | | | re: using conditional statements in where-clause
On 2 Feb, 06:40, Tim Roberts <t...@probo.comwrote: Quote:
"Captain Paralytic" <paul_laut...@yahoo.comwrote:
> Quote:
On 30 Jan, 13:56, "pwieg...@gmail.com" <pwieg...@gmail.comwrote: > Quote: Quote:
I'm trying to use the result of a conditional statement in a where
clause, but i'm getting 1)nowhere 2) desperate :-)
> Quote: Quote:
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
---------
> Quote: Quote:
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
> Quote: Quote:
This is not working. Nor is any other variant I can think of. How can
this be achieved?
> Quote: Quote:
greeting, and many thanks,
> > Quote:
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. Quote:
>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." |  |
| | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,471 network members.
|
