CODEWITHSUNDEEP
mysql
ATLChris
ATLChris

Reputation: 3296

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 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?

Upvotes: 19

Views: 81339

Answers (3)

Sudipta Kumar Dey
Sudipta Kumar Dey

Reputation: 1

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';

Upvotes: 0

nate c
nate c

Reputation: 9005

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.

Upvotes: 10

asm
asm

Reputation: 354

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.

Upvotes: 20

Related Questions