MySQL AS in WHERE statement

Question

I have a MySQL database. I use a SELECT AS to measure "distance" between 2 points. What I want to do is use the same "distance" variable I created in the SELECT as a WHERE condition.

SELECT first_name.last_name AS name WHERE name="John Doe"

What is the best way to accomplish this?

Answer 1

You may want to consider putting your condition in a HAVING clause rather than in the WHERE clause.

See http://dev.mysql.com/doc/refman/5.5/en/select.html for details on the HAVING clause.

Answer 2

Column aliases can only be referenced in the ORDER BY clause (as they do not exist yet in the WHERE part. You can do this (Although I do not necessarily recommend it as it may be slower.)

select name from
(select concat(firstname, ' ', lastname) as name from yourtable)
where name = 'John Doe'

Or you could do this:

select (firstname, ' ', lastname) as name from yourtable
where (firstname, ' ', lastname) = 'John Doe';

The only columns you have access to in the WHERE clause are the columns in the FROM clause.