获取行值仅在特定列的值不存在

Question

我在我的数据库students两个表：

GRADE_REPORT：

+----------------+-------------+------+ 
|StudentNo  | SectionId | Grade| 
+----------------+-------------+------+ 
| 11    | 85   | B |  
| 17    | 112   | B | 
| 17    | 119   | C |  
| 17    | 135   | A | 
| 8    | 85)   | A |  
| 8    | 92   | A | 
+----------------+-------------+------+ 

学生：

+----------------+-------------+------+--------+----------------+ 
| StudentNumber | name  | Classification| DepartmentName | 
+----------------+-------------+---------------+----------------+ 
| 17    | Bill Smith | 1    |  CS  |  
| 8    | Bob Brown | 2    |  CS  | 
| 11    | Nick Fiener | 4    |  MATH  |  
+----------------+-------------+------+-----+---------+---------+ 

我试图找回所有学生的学号，姓名和主要部门ho do在他们的课程中没有任何A等级。所以，我期待下，当我运行查询：

+----------------+-------------+----------------+ 
| StudentNumber | name  | DepartmentName | 
+----------------+-------------+----------------+ 
| 11    | Nick Fiener | MATH   | 

这是我的发言至今：

SELECT students.STUDENT.StudentNumber, students.STUDENT.name, 
    students.STUDENT.DepartmentName 
FROM students.GRADE_REPORT 
INNER JOIN students.STUDENT 
ON students.GRADE_REPORT.StudentNo = students.STUDENT.StudentNumber 
WHERE 
((students.GRADE_REPORT.Grade = "B" 
    OR 
    students.GRADE_REPORT.Grade = "C") 
    AND students.GRADE_REPORT.Grade != "A") 
GROUP BY students.GRADE_REPORT.StudentNo; 

编辑：

SELECT students.STUDENT.StudentNumber, students.STUDENT.name, students.STUDENT.DepartmentName 
    FROM students.STUDENT S 
     WHERE NOT EXISTS(SELECT students.GRADE_REPORT.StudentNo 
                FROM students.GRADE_REPORT gr 
                 WHERE gr.StudentNo = S.StudentNumber 
                  AND gr.Grade = "A"); 

Answer 1

使用not exists：

select s.* 
from students s 
where not exists (select 1 
        from grade_report gr 
        where gr.student_no = s.student_no and gr.grade = 'A' 
       );