CODEWITHSUNDEEP
mysqlsql
brewphone
brewphone

Reputation: 1366

MySql, exclude a subset with association table

Have 2 tables. Table "students" with column "student_id" and "score". Association table "class_student" with column "class_id" and "student_id". How to list class_ids that all student scores of the class are over 80. Which means if the class has any student score less than 80 then the class won't be listed.

Upvotes: 0

Views: 37

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

I would use min()for this purpose:

select cs.class_id
from class_student cs join
     students s
     on s.student_id = cs.student_id
group by cs.class_id
having min(s.score) >= 80;

Upvotes: 1

juergen d
juergen d

Reputation: 204756

Group by the classes and take only those groups having no record with your specified condition

select cs.class_id
from class_student cs
join students s on s.student_id = cs.student_id
group by cs.class_id
having sum(s.score < 80) = 0

Upvotes: 0

Related Questions