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.

Popular Pages

  1. Fast rounded corners in Photoshop (7424 recent visits)
  2. PHP – passing variables across pages (2558 recent visits)
  3. JavaScript set selected on load (2414 recent visits)
  4. Removing all child nodes from an element (1828 recent visits)
  5. Firefox 3 smart address bar: wildcard search (1755 recent visits)
  6. iPod songs out of order? (1314 recent visits)
  7. Britney - Everytime piano tab (1138 recent visits)
  8. MySQL LEFT JOIN syntax (884 recent visits)
  9. Firefox 3 smart address bar: wildcard search (722 recent visits)
  10. Date difference in MySQL (651 recent visits)

Similar Entries

Stats

225 unique visits since July 2008

Syndicate

Advertisements