How to use select (query)alias on WHERE clause?
Renaming takes place after WHERE. You can either repeat the CASE statement:

SELECT res_ID,res_fName,res ...
   (case ... end) as Age_Stage
FROM resident_detail rd
LEFT JOIN ...
WHERE (case ... end) = 'Young Adult'


or nest the statement:

SELECT ...
FROM (
    SELECT res_ID,res_fName,res ...
       (case ... end) as Age_Stage
    FROM resident_detail rd
    LEFT JOIN ...
) AS T
WHERE Age_Stage = 'Young Adult'


Or you can try add it in an HAVING clause. As noted by ypercubeᵀᴹ in a comment, MySQL deviates from the standard by evaluate HAVING before renaming ( ... as Age_Stage). This is a trick that appears to be quite commonly used for MySQL in situations like this:

SELECT res_ID,res_fName,res ...
   (case ... end) as Age_Stage
FROM resident_detail rd
LEFT JOIN ...
HAVING Age_Stage = 'Young Adult'