MySQL search criteria - column alias

December 12, 2005 / Filed under: SQL

I came across a abnormal thing this morning - well... it seems abnormal to me.

It involves using a column’s alias as criteria in a MySQL query result.

And alias is simply a "custom name," which can be applied to any field returned in a result set. This is helpful for fields that return the result of an expression, and not just the straight-forward field value. Since that expression is, by default, used as the "column name," and can often be a very long expression - the column name stretches out as far as the expression does, which is not practical.

To help understand this babble - a column I am using has a date stamp - for the date each record is entered into the database.

I’d like to run a query, using today’s date, to find out the number of days in between today’s date, and the date the row was entered into the database.

So, for instance - let’s say a row was entered into the database yesterday.

The number I am after is 1, which is the number of days between today, and yesterday.

Here is some sample SQL syntax:

TO_DAYS( CURDATE() ) - TO_DAYS( entryDate ) AS 'number of days'

This expression subtracts the entryDate from the current date. So I will always be presented with the number of days "elapsed," if you want to call it that.

Notice I’ve also given this expression an alias ('number of days'), so it’s easier to reference, later on.

Include only 2 days

Let’s say I want to have my query only return rows with two days elapsed. In other words, two days, based on the expression from above.

So, the WHERE clause, in my query, could look like this:

... WHERE 'number of days' = 2

Except... this won’t work. You can’t use an alias, as a reference in the WHERE clause - specifically if that alias is the result of an expression.

Pooey.

So, I had to modify that to be:

... WHERE TO_DAYS( CURDATE() ) - TO_DAYS( entryDate ) = 2

As you can see, I had to repeat the entire expression, in the WHERE clause, when it already exists in the SELECT clause.

But it’s good information to know.

Comments/Mentions

# Kristian at 2/21/2006 2:50 pm cst

I totally agreed with you, until I remember the little used HAVING clause...
http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html

# Member at 9/28/2006 7:09 am cst

can we use alias column name is queries

# Nathan Roberts at 11/28/2008 2:29 am cst

Brilliant just what I was looking for repeat the expression in the where clause I spent a day trying to figure this out for a whole day