WHERE clause column alias

Question

Problem

I am using an alternate column name (alias) in Query, I can use the alias "given_name" as part of ORDER BY, but I cannot use it as part of the WHERE clause. WHERE "given_name" is passed as a result of a request from my control, and I don’t know the actual column name to be used in the WHERE clause.

Question

• Is there a way / hack using a column alias in a WHERE clause?
• Is there a way to find the column name from an alias?

Study

After some research, it looks like an alias added after the WHERE clause.

Example

SELECT profile.id AS id, given.name AS 'given_name', family.name AS 'family_name' FROM green_profile profile LEFT JOIN green_name given ON given.profileid = profile.id AND given.name_typeid = 0 LEFT JOIN green_name family ON family.profileid = profile.id AND family.name_typeid = 1 WHERE given_name LIKE 'levi%' ORDER BY given_name DESC LIMIT 0 , 25 

Answer 1

Unconfirmed, but this hack should work ...

 SELECT * FROM ( SELECT profile.id AS id, given.name AS 'given_name', family.name AS 'family_name' FROM green_profile profile LEFT JOIN green_name given ON given.profileid = profile.id AND given.name_typeid = 0 LEFT JOIN green_name family ON family.profileid = profile.id AND family.name_typeid = 1 ) as temptable WHERE given_name LIKE 'levi%' ORDER BY given_name DESC LIMIT 0 , 25 

It works by simply creating a temporary table from your original select statement (without the where and ordering clause), which indicates the columns you specify. Then you select the desired column names from it.

A better approach would be to create a view with the desired column names and select from the view ...

 CREATE VIEW newtable AS SELECT profile.id AS id, given.name AS 'given_name', family.name AS 'family_name' FROM green_profile profile LEFT JOIN green_name given ON given.profileid = profile.id AND given.name_typeid = 0 LEFT JOIN green_name family ON family.profileid = profile.id AND family.name_typeid = 1; 

And then...

 SELECT * FROM newtable WHERE given_name LIKE 'levi%' ORDER BY given_name DESC LIMIT 0 , 25 

Answer 2

You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses.

Standard SQL does not allow reference to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value cannot yet be determined.

Answer 3

In doubt, simply refer to the column by number:

 ... ORDER BY 2 ...