Unknown column in 'having clause' in mysql 5.5

Question

i try to run a query with having:

        SELECT 
            `doctors`.*,
            (
                SELECT GROUP_CONCAT(`areas`.`areaName` SEPARATOR ', ')
                FROM `areas_has_doctors`
                INNER JOIN `areas` ON `areas`.`areaId` = `areas_has_doctors`.`areaId`
                WHERE `areas_has_doctors`.`doctorId` = `doctors`.`doctorId`
            ) as `areas`,
            (
                SELECT GROUP_CONCAT(`areas`.`areaId`)
                FROM `areas_has_doctors`
                INNER JOIN `areas` ON `areas`.`areaId` = `areas_has_doctors`.`areaId`
                WHERE `areas_has_doctors`.`doctorId` = `doctors`.`doctorId`
            ) as `areasIdies`
        FROM 
            `cats_has_doctors`
            INNER JOIN `doctors` ON `doctors`.`doctorId` = `cats_has_doctors`.`doctorId` 
        WHERE 
            `cats_has_doctors`.`catId` = '1' && `doctors`.`disable` = 0
        GROUP BY 
            `cats_has_doctors`.`relationId`
         HAVING FIND_IN_SET('1,2,3,4', `areasIdies`)

Server with 10.0.21-MariaDB - MariaDB Server its working but in server with:

5.5.61-cll - MySQL Community Server (GPL)

i got the error:

Unknown column 'areasIdies' in 'having clause'

what can i do ?

`HAVING` part applies earlier than `SELECT`. Put the original query in place of alias. — revo, Dec 05 '18 at 14:21
"HAVING part applies earlier than SELECT" @revo not sure what you mean i assume you mean `HAVING` can't use aliases from the same `SELECT` query? if that is what you mean you are wrong. — Raymond Nijland, Dec 05 '18 at 14:28
basically, `HAVING` doesn't get along with aliases... you'd need to specify a `HAVING + full subquery` or enclose everything except for the having into a one big query, then use having in the final output (which would probably mess with mySQL's optimization planning) — Javier Larroulet, Dec 05 '18 at 15:40
The `FIND_IN_SET` is backward: The needle comes first, the haystack second. Or you are trying the impossible: checking multiple keys. — Rick James, Dec 05 '18 at 17:41
The manual states: "This function does not work properly if the first argument contains a comma (,) character. " — Rick James, Dec 05 '18 at 17:58
It seems an bug in the interpretation of the backtick (\`) in the `HAVING` condition, only MySQL 5.5. See [db-fiddle](https://www.db-fiddle.com/f/mHXTpoUZAGukUVypSAjtLJ/1). — wchiquito, Dec 06 '18 at 12:18

score 0 · Answer 1 · answered Dec 05 '18 at 15:43

Maybe it thinks areasIdies is a variable?

I would try pulling the areasIdies subquery into a temp table & trying that. Just keep the query as it is to test, but just do that at the top and change the find in set param.

CREATE TEMPORARY TABLE IF NOT EXISTS areasIdiesTemp AS (
    SELECT GROUP_CONCAT(`areas`.`areaId`)
    FROM `areas_has_doctors`
    INNER JOIN `areas` 
    ON `areas`.`areaId` = `areas_has_doctors`.`areaId`
    WHERE `areas_has_doctors`.`doctorId` = `doctors`.`doctorId`
);

SELECT 
    `doctors`.*,
    (
        SELECT GROUP_CONCAT(`areas`.`areaName` SEPARATOR ', ')
        FROM `areas_has_doctors`
        INNER JOIN `areas` ON `areas`.`areaId` = `areas_has_doctors`.`areaId`
        WHERE `areas_has_doctors`.`doctorId` = `doctors`.`doctorId`
    ) as `areas`,
    (
        SELECT GROUP_CONCAT(`areas`.`areaId`)
        FROM `areas_has_doctors`
        INNER JOIN `areas` ON `areas`.`areaId` = `areas_has_doctors`.`areaId`
        WHERE `areas_has_doctors`.`doctorId` = `doctors`.`doctorId`
    ) as `areasIdies`
FROM 
    `cats_has_doctors`
    INNER JOIN `doctors` ON `doctors`.`doctorId` = `cats_has_doctors`.`doctorId` 
WHERE 
    `cats_has_doctors`.`catId` = '1' && `doctors`.`disable` = 0
GROUP BY 
    `cats_has_doctors`.`relationId`
HAVING FIND_IN_SET('1,2,3,4', `areasIdiesTemp`);

Of course, I would refactor this to then use the temp table, but I can't fully test without the data sets, so God speed. — Tanner_Gram, Dec 05 '18 at 15:45

Unknown column in 'having clause' in mysql 5.5

1 Answers1