mysql - 如何获取第二个表中没有条目的记录

Question

对于前

部门

id  departmentname
1     x
2     y
3     z

员工

fkdepartmentid empname
         1      john
         1      sam
         2      ram
         3      hari    

此处，一个 empname 可以属于任意数量的部门。

我的要求是从 department 表中获取 empname!=john (带有联接)的所有部门。

我尝试使用以下查询:

SELECT d.id FROM department d
 INNER JOIN employee e ON d.id=e.fkdepartmentid
  WHERE((e.empname<>'1')OR d.id IN (SELECT DISTINCT fkdepartmentid FROM employee WHERE fkdepartmentid NOT IN (SELECT DISTINCT fkdepartmentid FROM employee WHERE empname=sam)) ) GROUP BY d.id

但是，查询速度很慢，并且在某些情况下会失败。结果应该是 2 和 3。我怎样才能达到这些结果？

Answer 1

Here you go

SELECT * FROM department WHERE id IN( SELECT fkdepartmentid FROM employee WHERE empname !='john' GROUP BY fkdepartmentid )