complex qurey- retrieve employee working in two departments

November 4, 2021

Im trying to figure a way that can retrieve an employee working in two different departments
I have 3 simple tables which is
employee(employee_id,employee_name)
department(department_id,department_name)
working(eid,did,work_time)
so I have tried to write an sql command

 select employee_name
    from employee,working,department
    where eid=employee_id and did= department_id 
    and department_name= 'software'and dname='hardware';

and it doesn’t work , What is my problem?

>Solution :

The problem is that you are requiring department to be both ‘software’ and ‘hardware’. Also, dname is not a field.

Correcting your query:

select employee_name
from employee, working, department
where eid = employee_id and did = department_id 
and (department_name = 'software' or department_name = 'hardware');

But I would prefer this kind of query:

SELECT DISTINCT e.employee_name
FROM employee e
JOIN working w ON w.eid = e.employee_id
JOIN department d ON d.department_id = w.did
WHERE d.department_name IN ('software', 'hardware');

That is to get employees that work in any of the two departments (or both).

If you want only employees that work in both departments, try this:

SELECT e.employee_id, e.employee_name
FROM employee e
JOIN working w ON w.eid = e.employee_id
JOIN department d ON d.department_id = w.did
WHERE d.department_name IN ('software', 'hardware')
GROUP BY e.employee_id HAVING COUNT(DISTINCT d.department_id) = 2;