alexa

PostgreSQL - Query to list all students having atleast one mark different than 0 in SQL ?

PostgreSQL - Query to list all students having atleast one mark different than 0 in SQL ?

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

288 0
7

Write a Comments


* Be the first to Make Comment

GoodFirms Badge
GoodFirms Badge

Fix Your Meeting With Our SEO Consultants in India To Grow Your Business Online

Facebook
Twitter
LinkedIn
Instagram
Whatsapp
Call Now
Quick Inquiry