mysql get result from join table

I have a tables person and exam.

person

-------------
id  | name
-------------
1   | J.Doe
2   | Snow

exam
----------------------------------------------------
id | person_id | exam_no | exam_status | pass_date 
----------------------------------------------------
1  | 1         | 1       | PASS        | 2020-01-01
2  | 1         | 2       | PASS        | 2020-01-10
3  | 1         | 3       | PASS        | 2020-01-20
4  | 2         | 1       | PASS        | 2020-01-01
5  | 2         | 2       | NOT PASS    | NULL
6  | 2         | 3       | NOT PASS    | NULL

I want to show data where all exam is PASS, show the latest exam pass_date. like table below

----------------------
person_id | pass_date
----------------------
1         | 2020-01-20
2         | NULL

what query to get it? please help. thanks!

Answer

You can use aggregation and conditional expressions:

select 
    person_id,
    case when max(exam_status = 'NOT PASS') = 0 then max(pass_date) end pass_date
from exam
group by person_id

Note that for this sample data, you don’t need to bring in the person table to get the results that you want.