Search code examples
mysqlaliassql-like

Mysql Like for alias column

I would like to search in an alias column with this query:

SELECT *, (SELECT `name` FROM agent WHERE agent.`id`=sys_users.`agent_id`) as agentName FROM `sys_users` where agentName like '%company%'

but when i execute the query, MySQL tell me:

Error Code: 1054 Unknown column 'agentName' in 'where clause'

Solution

  • Standard SQL disallows references to column aliases in a WHERE clause (see the manual). You need to use HAVING instead i.e.

    SELECT *, 
   (SELECT `name` FROM agent WHERE agent.`id`=sys_users.`agent_id`) as agentName 
   FROM `sys_users` HAVING agentName like '%company%'

    Since you want to be able to OR conditions it is probably better to use a JOIN i.e.

    SELECT *
    FROM sys_users JOIN agent ON agent.id = sys_users.agent_id
    WHERE sys_users.username LIKE '%company%' OR agent.name LIKE '%company%'

    Note that in this case you will need to be careful of cases where sys_users and agent have colums with the same name, so it is probably better to explicitly list the columns you want.