How to use select (query)alias on WHERE clause? [duplicate]

Question

I'm having a problem with my query, this is the working query

    SELECT res_ID,res_fName,res_mName,res_lName,rs.suffix,rms.marital_Name,rg.gender_Name,rr.religion_Name,ro.occupation_Name,ros.occuStat_Name,res_Date_Record,rc.country_citizenship,res_Bday,TIMESTAMPDIFF(YEAR,res_Bday,CURDATE()) AS age,
(case  
 when (TIMESTAMPDIFF(Month,res_Bday,CURDATE())<=1) then 'Maternal and Newborn'
 when (TIMESTAMPDIFF(Month,res_Bday,CURDATE())<=1 and TIMESTAMPDIFF(Month,res_Bday,CURDATE())<=12) then 'Babies'
when (TIMESTAMPDIFF(Month,res_Bday,CURDATE())<=13 and TIMESTAMPDIFF(Month,res_Bday,CURDATE())<=24) then 'Toddlers'
when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=2 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=4) then 'Preschoolers'
 when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=5 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=8) then 'School Age Children'
 when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=9 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=12) then 'Tweens '
when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=13 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=19) then 'Teenager'
when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=20 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=35) then 'Young Adult'
when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=36 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=55) then 'Middle-Aged Adults'
when (TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=56 and TIMESTAMPDIFF(Year,res_Bday,CURDATE())<=100) then 'Senior'
   end) Age_Stage
FROM resident_detail rd 
LEFT JOIN ref_suffixname rs ON rd.suffix_ID = rs.suffix_ID 
LEFT JOIN ref_gender rg ON rd.gender_ID = rg.gender_ID
LEFT JOIN ref_marital_status rms ON rms.marital_ID = rd.marital_ID
LEFT JOIN ref_religion rr ON rr.religion_ID = rd.religion_ID 
LEFT JOIN ref_occupation ro ON ro.occupation_ID = rd.occupation_ID 
LEFT JOIN ref_occupation_status ros ON ros.occuStat_ID = rd.occuStat_ID
LEFT JOIN ref_country rc ON rc.country_ID = rd.country_ID

this is the Result:

I want to use the alias "Age_Stage" column on the WHERE statement, on the image above "Age_Stage" has their specific value coming from the case statement, I just wanted to filter those "Age_Stage" values. but when I add on the query WHERE Age_Stage = "Young Adult" on the of query, the error is the result is

Unknown column 'Age_Stage' in 'where clause'

Answer 1

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'