MySQL search criteria - column alias Dec12 '05

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.

Categories: SQL

Add Feedback (view all)

Leave feedback

Feedback

Input format: The editor controls below will assist with Markdown syntax.

Status

Sub-status

Your info

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 ... Read more.

can we use alias column name is queries ... Read more.

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 ... Read more.

matthom is published and produced by Matt Thommes - an independent publishing enthusiast, mobile blogger, content creator, informative writer, web developer from Chicago. Never one to conform, Matt intends to promote the effect the web has on our lives, in an effort to intensify, instruct, and clarify all that is happening around us.

Contact Matt

Similar Entries

Stats

1129 unique visits since August 2008

Syndicate

Advertisements