CODEWITHSUNDEEP
mysqlsql
mahes
mahes

Reputation: 93

How can I use alias name in where clause in MySQL?

how can i use alias name in where clause in mysql

SELECT 
    *,
    CASE roles.rol_active
        WHEN '1' THEN 'yes'
        WHEN '0' THEN 'no'
    END AS roles_active
FROM
    roles
WHERE
    rol_is_deleted = 
 AND (rol_name LIKE '%ac%' 
  OR rol_display_name LIKE '%ac%'
  OR rol_description LIKE '%ac%'
  OR rol_active LIKE '%ac%'
  OR rol_updated_by LIKE '%ac%'
  OR rol_updated_at LIKE '%ac%')
ORDER BY rol_name asc
LIMIT 10 OFFSET 0;

Upvotes: 1

Views: 986

Answers (2)

jarlh
jarlh

Reputation: 44795

Wrap a part of your current query up in a derived table:

select * from
(
SELECT 
    *,
    CASE roles.rol_active
        WHEN '1' THEN 'yes'
        WHEN '0' THEN 'no'
    END AS roles_active
FROM
    roles
) as dt
WHERE
    rol_is_deleted = 
 AND (rol_name LIKE '%ac%' 
  OR rol_display_name LIKE '%ac%'
  OR rol_description LIKE '%ac%'
  OR rol_active LIKE '%ac%'
  OR rol_updated_by LIKE '%ac%'
  OR rol_updated_at LIKE '%ac%')
ORDER BY rol_name asc
LIMIT 10 OFFSET 0;

You can even have two separate WHERE clauses. One for column conditions in the sub-query, and another one for column alias conditions in the outer query. I.e. something like:

...
WHERE column-conditions
) as dt
WHERE column-alias-conditions
...

Upvotes: 3

potashin
potashin

Reputation: 44601

You actually can't, because select clause is evaluated after the where clause (there are no aliases at that point). However, you can use a having clause instead or use a temporary table.

Upvotes: 3

Related Questions