MySQL alias, variables and the WHERE clause
What a nice morning i had remembering that you cannot reference an ALIAS or a variable in a WHERE clause. So for anyone else that has a memory like mine (i'm jealous of fish put it that way)
standard SQL doesn't allow you to refer to a column alias in a where cause. Which make total sense (now) as the column value is most likely not determined at time of execution. So....
SELECT
@total := date_add(datefield, INTERVAL 1 YEAR) AS newDate
FROM
mytable
#WHERE
# @total > now() /*Invalid*/
#WHERE
# newDate > now() /*Invalid*/
@total := date_add(datefield, INTERVAL 1 YEAR) AS newDate
FROM
mytable
#WHERE
# @total > now() /*Invalid*/
#WHERE
# newDate > now() /*Invalid*/
Comments
SELECT
@total := date_add(datefield, INTERVAL 1 YEAR) AS newDate
FROM
mytable
HAVING @total > now()
@total := date_add(datefield, INTERVAL 1 YEAR) AS newDate
FROM
mytable
HAVING @total > now()
Great help for me. Thanks Andy for the question, and thanks Mike for the answer! ;)
Ya, Thanks Mike!
Thanks for the tip... Now... How would you accomplish this?
I was trying to figure out why my query wouldn't work. Thanks for posting this.
i think you meant...
#WHERE
# @total > now() /*valid*/
#WHERE
# newDate > now() /*Invalid*/
also what is the colon for? :=
SELECT
@total := date_add(dat.....
#WHERE
# @total > now() /*valid*/
#WHERE
# newDate > now() /*Invalid*/
also what is the colon for? :=
SELECT
@total := date_add(dat.....
cheeto wrote:
> i think you meant...
> #WHERE
> # @total > now() /*valid*/
No, I don't believe using a variable there is a truly valid solution. Variables change values when you least expect them to. You should only use variables as a way to store information *between* queries, not *within* a query, AFAIK.
A solution I've found elsewhere is that you can use the column alias in your HAVING clause. (http://dev.mysql.com/doc/refman/5.0/en/problems-wi...)
> i think you meant...
> #WHERE
> # @total > now() /*valid*/
No, I don't believe using a variable there is a truly valid solution. Variables change values when you least expect them to. You should only use variables as a way to store information *between* queries, not *within* a query, AFAIK.
A solution I've found elsewhere is that you can use the column alias in your HAVING clause. (http://dev.mysql.com/doc/refman/5.0/en/problems-wi...)
Hey,
You can use HAVING instead of WHERE in this case, it works perfectly fine and the difference is :
[quote]
The WHERE statement is executed to determine which rows should be included in the GROUP BY part, whereas HAVING is used to decide which rows from the result set should be used.
[/quote]
[refrence]
http://dev.mysql.com/doc/refman/5.0/en/problems-wi...
[/refrence]
Hope it helps,
MajiD
You can use HAVING instead of WHERE in this case, it works perfectly fine and the difference is :
[quote]
The WHERE statement is executed to determine which rows should be included in the GROUP BY part, whereas HAVING is used to decide which rows from the result set should be used.
[/quote]
[refrence]
http://dev.mysql.com/doc/refman/5.0/en/problems-wi...
[/refrence]
Hope it helps,
MajiD
What MajiD said, I couldn't use variables nor aliases in the WHERE clause either, but the HAVING clause worked perfectly.
Yeah, this is what HAVING is for. You can use it without a GROUP BY clause as long as the alias you're using came from an aggregate function.
http://dev.mysql.com/doc/refman/5.0/en/problems-wi...
http://dev.mysql.com/doc/refman/5.0/en/problems-wi...
