complex qurey- retrieve employee working in two departments-CodePudding

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?

CodePudding user response：

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;

CodePudding user response：

Would something like this work for you?

SELECT
   count(*) as cnt,
   employee.employee_name
FROM
   employee
   JOIN working ON working.eid = employee.employee_id
   JOIN department ON department.department_id = working.did
WHERE
   department.department_name = 'software' or department.department_name = 'hardware'
GROUP BY employee.employee_name
HAVING cnt > 1

This would count each employee who is linked both software or hardware department. Or you can leave WHERE clause away to get all employees working more than one departments.

CodePudding user response：

What is my problem?

There is no dname column in your tables.

You can simplify the problem as you don't need the department table since the working table contains the department id in the did column.

Then you need to GROUP BY each employee and find those HAVING a COUNT of two DISTINCT department ids:

SELECT MAX(e.employee_name)
FROM   employee e
       INNER JOIN working w
       ON e.employee_id = w.eid
GROUP BY e.employee_id
HAVING COUNT(DISTINCT w.did) = 2

If you want to consider only the software and hardware departments then:

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

CodePudding user response：

You can easily obtain employees who work in one specific department:

select *
  from Employee e inner join
       Working w on e.employee_id = w.eid inner join
       Department d on w.did = d.department_id
 where d.name = 'software'

Now ambiguity cames. If you want to get all employees work either in software or in hardware:

-- Employees who work at either software or hardware or both departments
select *
  from Employee e inner join
       Working w on e.employee_id = w.eid inner join
       Department d on w.did = d.department_id
 where d.name = in ('software', 'hardware')

If you want to get employees who works in both software and hardware departments:

-- Employees who work in both hardware and software deparments simultaneously 
select *
  from Employee e inner join
       Working w on e.employee_id = w.eid inner join
       Department d on w.did = d.department_id
 where d.name = 'software'

intersect

select *
  from Employee e inner join
       Working w on e.employee_id = w.eid inner join
       Department d on w.did = d.department_id
 where d.name = 'hardware'