对于前

部门

id  departmentname
1     x
2     y
3     z


雇员

fkdepartmentid empname
         1      john
         1      sam
         2      ram
         3      hari


在这里,一个empname可以属于任意数量的department

我的要求是从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。如何获得这些结果?

最佳答案

Here you go

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

10-08 12:38