Del Harvey – Developer

In SQL, you cannot use a column alias in the where clause that's been defined in the select clause, for example an alias created by a sub-select.

Today i discovered a derived temporary table workaround, this creates a table with my select statement and this allows me to use the alias with a conditional statement.

For example, i wanted to use the COUNT() aggregate function to return the amount of posts assigned to a blog.

So my query was something like ....

SELECT b.*, (SELECT count(id) FROM posts p where b.id=p.blogId) as myCount FROM blogs b;

This will return all the fields from the blogs table and a count of all the associated posts.

However, some of these categories may not have posts assigned to them and i can't use the alias in the conditional statement.

The workaround i found was to use the following, create a temporary table from the original result set so that you can use a conditional statement with the result fields returned.

SELECT * FROM ([PASTE ORIGINAL QUERY HERE]) AS myDerivedTable WHERE myCount > 0

I have used "myDerivedTable" as my table name, but this can be any name you choose.

After adding my original query, this is how the full length query would look.

SELECT * FROM (SELECT b.*, (SELECT count(id) FROM posts p where b.id=p.blogId) as myCount FROM blogs b) AS myDerivedTable WHERE myCount > 0

This will only return results that have the myCount alias > 0.

I haven't really looked into the negative impact in using this technique, but for the small scale website it has been used for it was an ideal solution.