对于前
部门
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 )