Mysql: Using where comparison operator in outer query against select subquery result

Question

I am am trying to use a WHERE clause based on a scalar subquery result. The query executes correctly without the WHERE clause. With the WHERE clause I get Error Code: 1054. Unknown column 'available_services' in 'where clause'.

How can I achieve filtering based on the result of the subquery?

Also since subqueries can be pretty inefficient any suggestions of how to improve the query would be useful.

SELECT DISTINCT 
    `suppliers`.`id` AS `supplier_id`, 
    `suppliers`.`name`, 
    `suppliers`.`code`, 
    `suppliers`.`notes`, 
    (
        SELECT GROUP_CONCAT(
            `services`.`name` 
            ORDER BY `services`.`order` 
            SEPARATOR ', '
        ) 
        FROM `supplier_services` 
        LEFT JOIN `services` 
            ON `supplier_services`.`service_id` = `services`.`id` 
        WHERE 
            `supplier_services`.`service_id` = `services`.`id`
            AND `supplier_services`.`supplier_id` = `suppliers`.`id`
        GROUP BY `supplier_services`.`supplier_id`
    ) AS `available_services`
FROM `suppliers` 
WHERE `available_services` like '%pet%' 
GROUP BY `suppliers`.`id`

Answer 1

In case anyone comes across this and has a similar problem the reason (as pointed out by Nico in the comments) is that while you can use table aliases in where clauses you can not use field aliases. You can however have field aliases in HAVING clauses.

The solution is to use

GROUP BY `suppliers`.`id` 
HAVING `available_services` like '%pet%'

instead of

WHERE `available_services` like '%pet%'  
GROUP BY `suppliers`.`id`

Alternatively the alias in the where clause could be replaced with the subquery again but that may be inefficient or it may be cached by mysql and not be an issue, you would need to check that carefully if you use that solution.

Answer 2

Hi you should as variable in query if would you like to reach subquerys result

SELECT DISTINCT 
`suppliers`.`id` AS `supplier_id`, 
`suppliers`.`name`, 
`suppliers`.`code`, 
`suppliers`.`notes`, 
@available_services := (
    SELECT GROUP_CONCAT(
        `services`.`name` 
        ORDER BY `services`.`order` 
        SEPARATOR ', '
    ) 
    FROM `supplier_services` 
    LEFT JOIN `services` 
        ON `supplier_services`.`service_id` = `services`.`id` 
    WHERE 
        `supplier_services`.`service_id` = `services`.`id`
        AND `supplier_services`.`supplier_id` = `suppliers`.`id`
    GROUP BY `supplier_services`.`supplier_id`
) AS `available_services_as_column_view`
FROM `suppliers` 
WHERE @available_services like '%pet%' 
GROUP BY `suppliers`.`id`