SQL查询返回错误结果以获取部门

Question

我有一个像这样的表 Department:

DepartmentName    City            NumberOfEmployees    Country
----------------------------------------------------------------
Abc               California      100                  USA
Xyz               Chicago         200                  USA
Lmn               Sydney          300                  Aus
Pqr               Paris           400                  France

技术:

TechnologyId    Name     DepartmentName 
----------------------------------------
1               Hadoop     Abc
2               Hadoop     Abc
3               Hadoop     Xyz
4               Hadoop     Lmn
5               Adobe      Pqr
6               Adobe      Lmn
7               Adobe      Abc

这就是我想要做的:

• 获取同时拥有 Hadoop 和 Adob​​e 的所有部门

查询:

SELECT 
    Department.DepartmentName, Department.DepartmentName, 
    Department.DepartmentName, Department.DepartmentName, 
    Technologies.Name 
FROM 
    Department 
INNER JOIN 
    Technologies ON Department.DepartmentName = Technologies.DepartmentName
WHERE 
    (((Technologies.Name) IN ('Hadoop', 'Adobe')));

但这返回了错误的结果。

谁能帮我查询一下？

Answer 1

我会使用exists:

select d.*
from department d
where
    exists (
        select 1 
        from technologies t 
        where t.departmentname = d.departmentname and t.name = 'Hadoop'
    )
    and exists (
        select 1 
        from technologies t 
        where t.departmentname = d.departmentname and t.name = 'Adobe'
    )

有了 technologies(departmentname, name) 的索引，这应该是一个有效的选择。