How to filter specific entries from a MySql table I have a MySql table and it has following values.

Table name results.


    Reg.No   SubjectCode   Attempt   Pass/Fail
    112108   CMIS 1113           1   fail
    112110   CMIS 1114           1   pass
    112119   CMIS 1114           1   fail
    112108   CMIS 1113           2   fail
    112107   CMIS 1113           1   fail
    112108   CMIS 1113           3   pass


Following are the business rules.


Students can have several attempts to pass the subject.
Student should pass each subjects to get the degree.
Some student pass in first attempt.
Some take more than 3 attempt.
However student can try until he/she pass.
But some still remain fail.


So I want to get the Reg.No of students who are still unable to pass the subject.
(eg .112119 and 112107 still unable to pass their subject)

I was unable to write a query for this problem.

Can someone help me?

Thank you
This is what you need.

SELECT DISTINCT `original_results`.`Reg.No`
FROM  `results` AS `original_results`
WHERE NOT EXISTS (
    SELECT DISTINCT `helper_results`.`Reg.No`
    FROM `results` AS `helper_results` 
    WHERE `helper_results`.`Reg.No` = `original_results`.`Reg.No` AND `helper_results`.`Pass/Fail` = 'pass'
)