mysql - 查找并返回前员工但不是现任员工

Question

给定下表:

Staff
+------------+--------+
| employeeID |  name  |
+------------+--------+
|     100100 | Kelly  |
|     101010 | John   |
|     222222 | Stuart |
+------------+--------+

Academics
+------------+----------+
| employeeID | degreeID |
+------------+----------+
|     100100 | PhD      |
|     101010 | Eng      |
|     222222 | Sci      |
+------------+----------+

Class
+------------+-----------+-----------+
| employeeID | studentID | subjectID |
+------------+-----------+-----------+
|     100100 |       998 | BUS_18_2  |
|     100100 |       921 | BUS_18_2  |
|     100100 |       901 | BUS_18_2  |
|     100100 |       934 | BUS_19_1  |
|     100100 |       964 | BUS_19_2  |
|     100100 |       934 | LED_19_1  |
|     100100 |       964 | LED_19_2  |
|     101010 |       901 | COE_19_2  |
|     101010 |       874 | COE_19_2  |
|     101010 |       823 | COE_19_2  |
|     222222 |       212 | FTR_17_2  |
|     222222 |       102 | FTR_17_1  |
|     222222 |       684 | FTR_18_1  |
+------------+-----------+-----------+

返回2019年所有未上课教职工的姓名和学位ID列表

我已经尝试了各种方法来构建嵌套的 having 语句来检测工作人员是否已经工作多年(基于对 subjectID 的计数)，但这或多或少是“硬编码”，新条目可能会破坏这种方法(如示例所示)。

预期结果

+------------+------------+
|    name    | degreecode |
+------------+------------+
|   stuart   | sci        |
+------------+------------+

Answer 1

使用不存在的相关子查询

select distinct name, degreeid 
from staff a join academics b on a.employeeid=b.employeeid
join class c on a.employeeid=c.employeeid
where not exists 
   (select 1 from class c1 where c.employeeid=c1.employeeid and subjectID like '%_19%')