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

I have two tables in my database students:

我的数据库中有两个表格:

GRADE_REPORT:

GRADE_REPORT:

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

STUDENT:

学生:

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

I am trying to find retrieve the student number, name and major departments of all students who do not have any grade A in any of their courses. So I am expecting the following when I run the query:

我正在查找所有在他们的课程中没有任何A级的学生的学号、名字和主要系。因此，我在运行查询时，希望看到以下内容:

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

This is my statement so far:

这是我迄今为止的声明:

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;

EDITED:

编辑:

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");

1 个解决方案

#1

Use 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'
                 );