Let's say I have following sql tables:

Table "student"

id	first_name	last_name
1	Lillian	Nees
2	William	Lorenz
3	Mary	Moore
4	Giselle	Collins
5	James	Moultrie
6	John	Rodriguez

Table "exam_result":

exam_result_id	subject_id	student_id	mark
1	2	1	49
2	2	2	21
3	1	3	81
4	4	1	33
5	4	2	19
6	3	2	46
7	1	5	55
8	3	5	75
9	2	5	60
11	1	6	86
12	2	6	92
13	3	6	48
14	4	6	78

I need to select all students, who have all their exam marks <50 or haven't taken any exam at all.

So, in this case I need students with id 1, 2 and 4.

Closest thing I came up with is the following query, but it gives students with id 1, 2, 4 and 6. I don't need a student with id 6, since he has only 1 failed exam, not all of them.

SELECT DISTINCT s.id, s.first_name, s.last_name
FROM university.student s
LEFT JOIN
    university.exam_result er ON
        s.id = er.student_id
WHERE er.mark < 50 OR er.mark IS NULL;

I need it to work in both PostgreSQL and MariaDB, so I don't want anything specific to them.

CodePudding user response：

The query that works is:

SELECT DISTINCT s.id, s.first_name, s.last_name
FROM university.student s
WHERE NOT EXISTS(SELECT 1 FROM university.exam_result er WHERE s.id = er.student_id AND er.mark > 49);

Thanks, @jarlh for your comment.

CodePudding user response：

I would use a NOT EXISTS condition combined with a check for the marks

select s.*
from student s
where not exists (select * 
                  from exam_result er
                  where er.student_id = s.id)
   or 50 >= all (select er.mark
                 from exam_result er
                 where er.student_id = s.id)

CodePudding user response：

One option is doing this with aggregation, by ensuring the count of marks less than 50 is equal to all the count of their marks. When the student has 0 marks, the condition will still be satisfied.

SELECT s.id, s.first_name, s.last_name
FROM      student s
LEFT JOIN exam_result er ON s.id = er.student_id
GROUP BY s.id, s.first_name, s.last_name
HAVING COUNT(CASE WHEN er.mark < 50 THEN 1 END) = COUNT(er.mark)

Check the MariaDB demo and PostgreSQL demo.