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`

@Nico, yes that explains the issue. Although it raises some new questions and problems. Since this sql is auto generated needing to workout whether to use having or where will cause me a new headache. But that's a problem i will deal with. Thank you for the help. — user12372677, Nov 14 '19 at 16:33

score 0 · Answer 1 · answered Nov 14 '19 at 16:18

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`

While that stops mysql from returning an error it also returns an empty recordset, so something is not quite right. But it is an interesting idea and I might look into if there is a way to make it work. — user12372677, Nov 14 '19 at 16:52

score 0 · Answer 2 · answered Nov 14 '19 at 16:57

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.

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

2 Answers2