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

for ex

department

id  departmentname
1     x
2     y
3     z

employee

fkdepartmentid empname
         1      john
         1      sam
         2      ram
         3      hari

Here one empname can belong to any number of departments.

这里有一个empname可以属于任意数量的部门。

My requirement is get all the departments from department table where empname!=john (with a join).

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

I tried with the following query:

我试过以下查询:

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

However, the query is slow and failing in some scenarios. the results should be 2 and 3. How can I achieve those results?

但是,查询很慢并且在某些情况下失败。结果应该是2和3.我怎样才能实现这些结果?

3 个解决方案

#1

Here you go

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

#2

If understand correctly you want to exclude department 1 because Jhon is an employer of that department. If it so you need to reverse the conditions.

如果理解正确,您想要排除第1部门,因为Jhon是该部门的雇主。如果是这样你需要扭转条件。

Try

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

Output:

| ID | DEPARTMENTNAME |
-----------------------
|  2 |              y |
|  3 |              z |

Here is SQLFiddle demo

这是SQLFiddle演示

#3

here is one which does not use nested queries:

这是一个不使用嵌套查询的方法:

SELECT t1.*
FROM department AS t1
LEFT JOIN employee AS t2
ON t2.deptid = t1.id AND t2.name IN ('john', 'rari')
WHERE t2.name IS NULL;

this lists all departments which does not have any employee with the name in list.

这将列出所有没有任何员工名单的员工。

#1

Here you go

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

#2