3

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?

9
  • 2
    Wrap it inside inline view (subquery in FROM ) : SELECT * FROM (your_query)a WHERE a.id like ... or a.ofname like ...
    – a1ex07
    Sep 8 '15 at 15:07
  • You might repeat the subqueries, but they will be evaluated twice (MariaDB has subquery cache, MySQL afaik not yet). But you should rewrite that to joins, it is more performant and you can use the columns directly
    – jkavalik
    Sep 8 '15 at 16:52
  • See this question for the reasoning behind not being able to use your alias in the where clause. stackoverflow.com/questions/4001183/mysql-order-of-operations It is because even though you type in that order, that is not the order that MySQL reads it.
    – Josh Simar
    Sep 8 '15 at 22:46
  • @JoshSimar The question you linked has one incorrect and one irrelevant answer. Sep 9 '15 at 9:54
  • @ypercube If that is the case then what is the order of operations?
    – Josh Simar
    Sep 9 '15 at 16:28
3

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.

2
  • 1
    If wan_reports.name or .location could be null then you want to change those joins to left joins in the last example, and I would recommend always aliasing tables and referencing columns by table alias prefix.
    – BateTech
    Sep 11 '15 at 4:29
  • @BateTech Yep, forgot to add the note about a possible left join, thanks. I did not add aliases to keep the same style OP used so they can easily match the code to the original.
    – jkavalik
    Sep 11 '15 at 5:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.