MySQL Query cannot find column

Question

I have a query that runs fine, when I try to add one more column to where clouses it cannot find the column and gives an error.

SELECT '1' AS `row_count`, (
    SELECT 
        COUNT(*) 
    FROM 
        `attendances` 
    WHERE `program_sessions`.`id` = `attendances`.`program_session_id` 
    AND `attendances`.`deleted_at` IS NULL
) AS `attendances_count`
FROM
    `program_sessions`
LEFT JOIN `programs` ON `programs`.`id` = `program_sessions`.`program_id`
LEFT JOIN `program_categories` ON `program_categories`.`id` = `programs`.`program_category_id`
LEFT JOIN `service_areas` ON `service_areas`.`id` = `program_categories`.`service_area_id`
LEFT JOIN `locations` ON `locations`.`id` = `programs`.`location_id`
WHERE (
    LOWER(`program_categories`.`name`) LIKE "%3%" OR 
    LOWER(`programs`.`name`) LIKE "%3%" OR 
    LOWER(`locations`.`name`) LIKE "%3%" OR
    (attendances_count = 3) OR 
    LOWER(`service_areas`.`name`) LIKE "%3%"
) 
AND `program_sessions`.`deleted_at` IS NULL

MySQL said: #1054 - Unknown column 'attendances_count' in 'where clause'

The query somehow cannot reach the attendances_count. What is it that I am doing wrong?

Are the counts coming from the correlated subquery in the `SELECT` clause even meaningful? You are counting over five joined tables. — Tim Biegeleisen, Mar 04 '18 at 23:54
@TimBiegeleisen Yes, it has to be so. They are simple tables though. — Ozan Kurt, Mar 04 '18 at 23:55
I'd explore the following for learning to see if it applies to your query: search for the`WITH` statement - https://stackoverflow.com/questions/3241352/using-an-alias-column-in-the-where-clause-in-postgresql and https://dev.mysql.com/doc/refman/8.0/en/with.html . But this isn't the simple solution referred to below (perhaps the optimization here is on the fact that you are just filtering where attendances_count = 3). But `WITH` is worth looking into for learning and see if it solves your problem, but again don't use it as a first preference since there seems to be a simpler solution mentioned — rishijd, Mar 05 '18 at 00:10

score 1 · Answer 1 · answered Mar 04 '18 at 23:52

1

Found the problem here, aparently where clouse is not able to see the aliased columns. I should use having instead.

Can you use an alias in the WHERE clause in mysql?

answered Mar 04 '18 at 23:52

Ozan Kurt

3,731
4
18
32

1

Yes, this would work (only on MySQL), but I think we can rewrite your query to avoid using `HAVING`. – Tim Biegeleisen Mar 04 '18 at 23:54
I would love to see how you would write it. – Ozan Kurt Mar 04 '18 at 23:55
@Strawberry I'm sure you can save me the time and tell the correct solution. – Ozan Kurt Mar 05 '18 at 00:02
What is wrong with you people pushing random developers to force themselves? If I had the time and knowledge, would I even ask the question? My mother can also tell me that... "I'm sure you'll extract greater benefit from figuring it out for yourself" – Ozan Kurt Mar 05 '18 at 00:07
You can certainly rewrite the query as a join to the `attendances` table. But I would need to see more data. Your current subquery looks very strange to me. – Tim Biegeleisen Mar 05 '18 at 01:41

MySQL Query cannot find column

1 Answers1