case statement which taken match of same status in mysql

Question

I have 2 tables, One table has status and another one table has mark and id.

table1

id	versin	name
1	0	ram
2	1	ram
3	2	ram
4	3	ram

table2

sid	status	name
1	Pass	ram
2	Pass	ram
3	Pass	ram
4	Fail	ram

this is my query,

select id, 
       case when status ='fail' or status='N/A' 
            then max(versin)-1
            when status = 'PASS' 
            then max(versin)
            else max(versin) 
            end as versin
from table1 A
inner join ( select max(versin) as mxversin 
             from table1 
             group by name ) B
    on B.id = A.id and B.mxversin = A.versin
left join table2 C 
    on C.name= B.name

In this scenario, I got correct versin, but the id is not matching.

I got,

table

id	versin	name
1	2	ram

I need

The result table should be

id	versin	name
3	2	ram

Please help me out from this scenario, thanks in advance

Answer 1

Test this:

SELECT t1.*
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.sid AND t1.name = t2.name
WHERE t2.status = 'Pass'
ORDER BY t1.versin DESC LIMIT 1

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e5c80834ad2662a47254c777adba1355