Mysql custom where condition
My table is :
I want to select records who all are fail(result=0) but except who has same course_id result is 1.
For example look my table there are two rows have result=0 but student_id=1 has another row with result=1 so i want skip this record.
Sorry for confusing you.I hope my expecting output explains clearly what I want.
Expecting output is :
2 | 1 | 0
SELECT student_id, course_id, result FROM tbl WHERE result = 0 AND student_id NOT IN (SELECT student_id FROM table WHERE result =1 and course_id = tbl.course_id )
Above we are selecting all records that have a result =0 only if the student_id and course_id is not found in the sub query with a result=1