To find all students that have at least one non-zero mark, I would use an EXISTS condition:
select stu.*
from student stu
where exists (select *
from mark mrk
where mrk.student_id = stu.id
and mrk.mark <> 0);
Finding students that only have zero marks is a bit more tricky and requires aggregation.
select stu.*
from student stu
where exists (select mrk.student_id
from mark mrk
where mrk.student_id = stu.id
group by mrk.student_id
having bool_and(mrk.mark = 0));
Logically, the group by
is not needed in the sub-query as it will only deal with a single student, but the SQL syntax rules require it.
This could also be expressed as a join against a derived table which might actually be a bit more efficient:
select stu.*
from student stu
join (
select mrk.student_id, bool_and(mrk.mark = 0) as all_marks_are_zero
from mark mrk
group by mrk.student_id
) m on m.student_id = stu.id and m.all_marks_are_zero
* Be the first to Make Comment