[Q] SELEC Alias

Hi all,
Why this Query doesn't work

SELECT users.id AS UserId
FROM users
WHERE users.UserId > 8

?

cause i wanna use it in this way

SELECT users.id AS UserId, FROM_UNIXTIME(users.regDate, '%M %Y') AS Date
FROM users
AND Date = 'November 2009'

---------------------------------------
i found out this :
I can't use Alias in where clause, so
in this example :
SELECT users.id AS UserId, FROM_UNIXTIME(users.regDate, '%M %Y') AS Date
FROM users
AND Date = 'November 2009'

it should be like this (forget about userId, focus on Date) :
SELECT users.id AS UserId, FROM_UNIXTIME(users.regDate, '%M %Y') AS Date
FROM users
AND FROM_UNIXTIME(users.regDate, '%M %Y') = 'November 2009'

is that is the only ugly way ??

thanks

 

yes that's the one good approach.
Else you can use derived table method.

 

No, there is another one too and much better in terms of query performance

SELECT users.id AS UserId, FROM_UNIXTIME(users.regDate, '%M %Y') AS Date
FROM users
[COLOR=Red]WHERE users.regDate BETWEEN
UNIX_TIMESTAMP('2009-11-01 00:00:00') AND 
UNIX_TIMESTAMP('2009-11-01 23:59:59')[/COLOR]

Code (markup):

 

here problem in your Query
the Correct Query will be this one.

SELECT users.id AS UserId
FROM users
WHERE users.id> 8

You can't use the Alias Name is where clause because that doesn't belong to table to match the criteria.