SELECT s.ssno , s.smno , s.question , s.objective1 , s.objective2 , s.objective3 , s.objective4 , s.objective5 , a.ano , b.subjectivity , b.objectivechoice , b.uno FROM tbl_survey_sub s LEFT OUTER JOIN (SELECT ssno , IFNULL( MAX(CASE uno WHEN 3 THEN ano END) , MAX(ano) ) ano FROM tbl_answer GROUP BY ssno ) a ON s.ssno = a.ssno LEFT OUTER JOIN tbl_answer b ON a.ssno = b.ssno AND a.ano = b.ano WHERE a.smno = 42 ORDER BY ssno ;