Why I cannot use this parameter?

Question

I'm trying to use the "id" in both selects on the UNION, is there any way to do it?

SELECT id, name, (

    SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a WHERE ag.id = id AND agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type != 3
    UNION
    SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a, user_analysis ua WHERE agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type = 3 AND ua.analysis_id = a.id AND a.id = '5bf1d6c5242f1' ORDER BY last_update LIMIT 1
) AS last_update_date 

FROM analysis_groups WHERE id != '0' ORDER BY last_update_date DESC LIMIT 20;

Answer 1

The short answer would be: Define an alias for analysis_groups in the main query, and reference it in the WHERE clause, like this

SELECT id, name, (

    SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a WHERE ag.id = ag2.id AND agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type != 3
    UNION
    SELECT DATE_FORMAT(a.time_added, '%d/%m/%Y') AS 'last_update' FROM analysis_groups ag, analysis_group_relation agr, analysis a, user_analysis ua WHERE agr.group_id = ag.id AND a.id = agr.analysis_id AND a.type = 3 AND ua.analysis_id = a.id AND a.id = '5bf1d6c5242f1' ORDER BY last_update LIMIT 1
) AS last_update_date 

FROM analysis_groups ag2 WHERE id != '0' ORDER BY last_update_date DESC LIMIT 20;