SQL query returning incorrect result to get departments

Question

I have a table Department like this:

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

Technologies:

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

This is what I am trying to do :

• Get all the departments that have both Hadoop and Adobe

Query:

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

But this is returning incorrect result.

Can anyone please help me with the query?

Answer 1

I would use 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'
    )

With an index on technologies(departmentname, name), this should be an efficient option.