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

我有一个像这样的表 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 和 Adobe 的部门

询问:

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')));

但这返回了错误的结果。

任何人都可以帮我查询吗？

最佳答案

我会使用 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) 上的索引，这应该是一个有效的选择。

关于SQL 查询返回错误结果以获取部门，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59803368/