This question already has an answer here:

i'm having a problem on my query, this is the working query and

    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,
 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:

enter image description here i want is use the alias "Age_Stage" column on WHERE statement, on the image above Age_Stage has their specific value, i just want is to filter those Age_Stage by value that i want. but when i add on the query WHERE Age_Stage = "Young Adult" on the of query the error is result is

Unknown column 'Age_Stage' in 'where clause'

marked as duplicate by Tom V, hot2use, marcello miorelli, mustaccio, McNets Apr 21 '18 at 20:22

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.


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
WHERE (case ... end) = 'Young Adult'

or nest the statement:

    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
HAVING Age_Stage = 'Young Adult'

Not the answer you're looking for? Browse other questions tagged or ask your own question.