What is wrong with this statement? 'Unknown column'?
A relational sub-select is logically processed in the following sequence:


FROM clause 
WHERE clause 
GROUP BY clause 
HAVING clause 
SELECT clause
ORDER BY clause 
LIMIT...OFFSET clause or the equivalent


Subsequently, the column labels defined in the SELECT clause are not yet known when the FROM clause, containing the joins in your example, is dealt with.