CODEWITHSUNDEEP
mysqlsqlselect
Martin
Martin

Reputation: 473

MySQL - SELECT AS in WHERE

For some reason, this doesn't work:

select substring(rating, instr(rating,',') +1, +2) as val
from users where val = '15';

It gives this error:

ERROR 1054 (42S22): Unknown column 'val' in 'where clause'

How do I do it then?

Upvotes: 30

Views: 65752

Answers (4)

Heider Sati
Heider Sati

Reputation: 2614

You sure can with MySQL, please see below, just tested this and it works fine:

Select substring(rating, instr(rating,',') +1, +2) as val 
From users 
Having val = '15';

You can also do things like "having val is null" etc.

With ALIASes you can't use WHERE, but using HAVING does the trick.

H

Upvotes: 10

echo_Me
echo_Me

Reputation: 37243

val is not defined, it's just an alias. Do it like this:

SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val 
FROM   users 
WHERE  SUBSTRING(rating, INSTR(rating,',') +1, +2) = 15

Upvotes: 4

刘娓娓
刘娓娓

Reputation: 1

You can use this query

SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2)
FROM   users 
WHERE  SUBSTRING(rating, INSTR(rating,',') +1, +2) = '15'

Upvotes: -2

John Woo
John Woo

Reputation: 263893

First, you cannot use ALIAS on the WHERE clause. You shoul be using the column,

SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val 
FROM   users 
WHERE  SUBSTRING(rating, INSTR(rating,',') +1, +2) = '15'

The reason is as follows: the order of operation is SQL,

  • FROM clause
  • WHERE clause
  • GROUP BY clause
  • HAVING clause
  • SELECT clause
  • ORDER BY clause

the ALIAS takes place on the SELECT clause which is before the WHERE clause.

if you really want to use the alias, wrap it in a subquery,

SELECT *
FROM
    (
        SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val 
        FROM   users
    ) s
WHERE   val  = '15'

Upvotes: 42

Related Questions