MySQL AS in WHERE statement

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 c开发者_运维知识库reated 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?

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.

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.

you can do one thing put the condition on where clause

SELECT *,
    IF(check_out='0000-00-00 00:00:00', NOW(), check_out) AS occ_test
FROM occupied_room1
WHERE
DATE(IF(check_out='0000-00-00 00:00:00', NOW(), check_out)) = ' 2017-07-02';