Table name : batch_1_student_marks
i am having student mark list in batch_1_student_marks table.
where "cleared" is representing the student pass/fail status.
cleared = 1 (pass)
cleared = 0 (fail)
In the following table student_id 2 is only having cleared status 1 in all rows. i dont want to select student_id 1 and 3 because it having cleared status 0 in corresponding rows.
if i use the following query it will give the result,
SELECT student_id from batch_1_student_marks WHERE student_id=2 and cleared=1. but this query is not suitable for student_id 1 and 3. because i dont want to select student_id if it has cleared 0 in even single row......... please help me to do a query...
|id| |semester| |student_id| |subject_id| |grade| |marks| |cleared|
1 -----1 --------------1-------------1-----------a---------8--------0
1 -----1 --------------1-------------2-----------b---------9--------1
1 -----1 --------------1-------------3-----------d---------2--------0
1 -----1 --------------1-------------8-----------e---------4--------1
1 -----1 --------------1-------------2-----------b---------9--------1
1 -----1 --------------1-------------3-----------d---------2--------1
1 -----1 --------------1-------------8-----------e---------4--------1
1 -----1 --------------1-------------2-----------b---------9--------0
1 -----1 --------------2-------------3-----------d---------2--------1
1 -----1 --------------2-------------8-----------e---------4--------1
1 -----1 --------------2-------------3-----------d---------2--------1
1 -----1 --------------2-------------8-----------e---------4--------1
1 -----1 --------------2-------------3-----------d---------2--------1
1 -----1 --------------2-------------8-----------e---------4--------1
1 -----1 --------------3-------------3-----------d---------2--------1
1 -----1 --------------3-------------8-----------e---------4--------0
1 -----1 --------------3-------------3-----------d---------2--------1
1 -----1 --------------3-------------8-----------e---------4--------0
1 -----1 --------------3-------------3-----------d---------2--------1
1 -----1 --------------3-------------8-----------e---------4--------0
Just use the following query:
SELECT student_id FROM batch_1_student_marks WHERE cleared='1';
You may use the following
If the cleared Field type is 'text' then:---
SELECT student_id FROM batch_1_student_marks WHERE cleared='1';
If the cleared Field type is 'number' then:---
SELECT student_id FROM batch_1_student_marks WHERE cleared=1;
I hope that it will help you.
SELECT student_id,cleared from batch_1_student_marks
GROUP BY student_id
HAVING COUNT(DISTINCT cleared)=1
AND cleared=1
You should have added the details from the start.A clear question almost answers itself.