PostgreSQL - Query to list all students having atleast one mark different than 0-CodePudding

I'm working on PostgreSQL and I'm trying to retrieve the list of students having atleast one mark different than 0 in a specific matter or simply filter the students having 0's everywhere in a specific matter here's my DB structure:

Student {
id,
name,
other info..
}

Mark {
id,
student_id,
matter,
mark
}

for example

Student(1,"John"..)
Mark(1,1,'Mathematics',0);
Mark(2,1,'Mathematics',0);
Mark(3,1,'Mathematics',0);

Mark(4,1,'Physics',15);
Mark(5,1,'Physics',12);
Mark(6,1,'Physics',11);


Student(2,"Albert"..)

Mark(7,2,'Mathematics',0);
Mark(8,2,'Mathematics',17);
Mark(9,2,'Mathematics',0);

Mark(10,2,'Physics',0);
Mark(11,2,'Physics',0);
Mark(12,2,'Physics',0);

Student(3,"Chris"..)

Mark(13,3,'Mathematics',0);
Mark(14,3,'Mathematics',0);
Mark(15,3,'Mathematics',0);

Mark(16,3,'Physics',0);
Mark(17,3,'Physics',0);
Mark(18,3,'Physics',0);

In this case we filter John mathematic marks and Albert's Physics marks and we don't display Chris at all

CodePudding user response：

You could try a basic join followed by filtering on records not having a zero mark:

SELECT DISTINCT s.id, s.name, m.matter
FROM Student s
INNER JOIN Mark m
    ON m.student_id = s.id
WHERE m.mark <> 0;

CodePudding user response：

You can do ti like this (result here)

select * 
from student s,mark m
where s.id = m.student_id
and m.mark > 0

CodePudding user response：

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

Online example