Can we use alias name in LIKE statement?

I have a query to search strings from database tables. The query is

SELECT  id
      , (SELECT office_name
         FROM   office_master
         WHERE  office_id = wan_reports.name
        ) AS ofname
      , (SELECT City_Name
         FROM   City_Master
         WHERE  City_Id = wan_reports.location
        ) AS lname
      , wan_ip
      , (SELECT office_class
         FROM   office_master
         WHERE  office_id = wan_reports.name
        ) AS class
      , last_online
      , last_check
      , attempts
FROM    wan_reports
WHERE   1 = 1
        AND (id LIKE 'B%'
             OR ofname LIKE 'B%'
             OR lname LIKE 'B%'
            );

Now here ofname in LIKE statement says unknown column. Is there any way to use alias in LIKE statement?

There are at least 3 different ways to do that. I will show all of them and add a link to sqlfiddle for each, but I strongly suggest you to adopt the last one.

You can wrap the query in another query and do the filtering "one level higher":

select * from (
  SELECT  id
      , (SELECT office_name
         FROM   office_master
         WHERE  office_id = wan_reports.name
        ) AS ofname
      , (SELECT City_Name
         FROM   City_Master
         WHERE  City_Id = wan_reports.location
        ) AS lname
      , wan_ip
      , (SELECT office_class
         FROM   office_master
         WHERE  office_id = wan_reports.name
        ) AS class
      , last_online
      , last_check
      , attempts
  FROM    wan_reports
) tmp
WHERE   1 = 1
        AND (id LIKE 'N%'
             OR ofname LIKE 'N%'
             OR lname LIKE 'N%'
            );

http://sqlfiddle.com/#!9/8c2a3/2

You can use HAVING (it is officially not supported as HAVING is supposed to work only on aggregated values, but it works really only as late WHERE in MySQL:

SELECT  id
      , (SELECT office_name
         FROM   office_master
         WHERE  office_id = wan_reports.name
        ) AS ofname
      , (SELECT City_Name
         FROM   City_Master
         WHERE  City_Id = wan_reports.location
        ) AS lname
      , wan_ip
      , (SELECT office_class
         FROM   office_master
         WHERE  office_id = wan_reports.name
        ) AS class
      , last_online
      , last_check
      , attempts
FROM    wan_reports
HAVING   1 = 1
        AND (id LIKE 'N%'
             OR ofname LIKE 'N%'
             OR lname LIKE 'N%'
            );

http://sqlfiddle.com/#!9/8c2a3/6

Usually the best is using joins instead of subqueries:

SELECT  id
      , office_name AS ofname
      , City_Name AS lname
      , wan_ip
      , office_class AS class
      , last_online
      , last_check
      , attempts
FROM    wan_reports
JOIN  office_master on (office_id = wan_reports.name)
JOIN  City_Master on (City_Id = wan_reports.location)
WHERE   1 = 1
        AND (id LIKE 'N%'
             OR office_name LIKE 'N%'
             OR City_Name LIKE 'N%'
            );

• If there is a possibility that the matching office/city will not exist, then you need to change the corresponding inner join to a left join. That would return NULL in joined columns in case no match was found.

http://sqlfiddle.com/#!9/8c2a3/5

Subqueries have a history of poor performance in MySQL. It is becoming better witch each new version but in the optimizer can still handle joins much better. And as you can see the join version is actually shorter for writting. You only have to use "original" columns names in WHERE even if you assign them aliases for output.

StackExchange Database Administrators Q#114477, answer score: 3