Retrieve full name of teacher for which all of his/her students have max score-CodePudding

I am having issues trying to retrieve the full name record of the teacher(only 1 record) for which all of his/her students have a maximum score(6). Here are the following 3 tables:

Students table:

student_id	teacher_id
1	2
2	1
3	2
4	3
5	3
6	4

Teachers table:

teacher_id	full_name
1	NameA
2	NameB
3	NameC
4	NameD

Scores table:

student_id	score
1	6
2	4
3	6
4	3
5	4
6	5

What I expect when having a proper query here is to retrieve a single row with 'NameB' teacher.

I have tried the following query:

SELECT t.full_name 
FROM students s 
INNER JOIN teachers t 
ON s.teacher_id = t.teacher_id 
WHERE s.student_id = 
    (
      SELECT COUNT(sc.student_id) 
      FROM scores sc 
      WHERE s.student_id = sc.student_id 
      AND sc.score = 6 
      GROUP BY sc.student_id
    );

When having only 1 teacher with students who have max score I am getting that teacher with the following query. But when I tried to test and have a second teacher which students have max score the query is not working.

Thanks a lot for your attention and time.

CodePudding user response：

If you want to select the teacher of which all of his/her student have only 6 score you can use HAVING NOT SUM(score <> 6) which will filter out all records which have a score different than 6.

Try:

select distinct t.full_name
from teachers t
inner join students s on t.teacher_id=s.teacher_id
inner join scores sc on sc.student_id=s.student_id
group by t.full_name
HAVING NOT SUM(sc.score <> 6)

https://dbfiddle.uk/qOrrVjuA