mysql select data from result of alias name on LEFT JOIN condition

Question

SELECT e.`event_type`,e.`event_status`,er.`event_reg_id`,er.`event_id`,'events' as category, 
    IF(e.`event_type`='C',e.`parent_id` ,IF(e.`event_type`='S',er.`event_id`,'') ) temp_id  
FROM `event_registration` er
    LEFT JOIN event e on er.`event_id`= temp_id WHERE er.`company_id`='%s' AND er.`student_id`='%S'

I take temp_id as alias name and give it to LEFT JOIN ON condition, it not works. I gave alias name instead of column name on LEFT JOIN ON Condition

Sebastian Brosch · Accepted Answer · 2018-07-26T18:00:04.877

You can not use the temp_id alias on the ON part of your query. The alias is not available there. But you can do the following moving the nested IF to the ON part:

SELECT
    e.`event_type`,
    e.`event_status`,
    er.`event_reg_id`,
    er.`event_id`,
    'events' AS category, 
    IF(e.`event_type` = 'C', e.`parent_id`, IF(e.`event_type` = 'S', er.`event_id`,'')) AS temp_id, 
FROM `event_registration` er
    LEFT JOIN event e ON er.`event_id`= IF(e.`event_type` = 'C', e.`parent_id`, IF(e.`event_type` = 'S', er.`event_id`,'')) 
WHERE er.`company_id` = '%s' AND er.`student_id` = '%S'

You can't use an alias from SELECT because the FROM part is executed earlier (see this answer on StackOverflow).

mysql select data from result of alias name on LEFT JOIN condition

1 Answers1