MySQL - access value of calculated column in SELECT

I have a query which produces some calculated columns using LEFT JOIN and GROUP_CONCAT:

SET @this_year = YEAR(CURDATE());  SET @next_year = YEAR(CURDATE()) +1;  SET @last_year = YEAR(CURDATE()) -1;    SELECT      m.id, m.name,        GROUP_CONCAT(`se1`.`name` ORDER BY `se1`.`id` ASC SEPARATOR ', ') AS this_year_events,      GROUP_CONCAT(`se2`.`name` ORDER BY `se2`.`id` ASC SEPARATOR ', ') AS next_year_months,      GROUP_CONCAT(`se3`.`name` ORDER BY `se3`.`id` ASC SEPARATOR ', ') AS last_year_months    FROM month m    LEFT JOIN schedule_event se1 ON      (m.start_date BETWEEN se1.start_date AND se1.end_date) OR      (m.end_date BETWEEN se1.start_date AND se1.end_date)        LEFT JOIN schedule_event se2 ON      (m.start_date BETWEEN se2.start_date AND se2.end_date) OR      (m.end_date BETWEEN se2.start_date AND se2.end_date)        LEFT JOIN schedule_event se3 ON      (m.start_date BETWEEN se3.start_date AND se3.end_date) OR      (m.end_date BETWEEN se3.start_date AND se3.end_date)    GROUP BY `m`.`id`  

The result looks like this:

id    name             this_year_events      next_year_events      last_year_events              ====================================================================================  1     January          Training, Planning    (NULL)                (NULL)                 2     February         (NULL)                (NULL)                Audit, Budget      3     March            Team Meeting          Team Away Day         (NULL)  

Now I want to add another column to this query: active_year. This column will be populated with the value of the corresponding variable based on the following condition:

DEFAULT:      active_year = @this_year    IF this_year_events IS NULL:      active_year = @next_year    IF next_year_events IS NULL:      active_year = @last_year  

(the value of active_year would overwrite the previous value if the subsequent condition is met)

I have tried assigning a variable to the 3 calculated columns but it always outputs NULL. I just can't seem to figure this out.

https://stackoverflow.com/questions/66773349/mysql-access-value-of-calculated-column-in-select March 24, 2021 at 09:02AM