What would be the query for Employee who work for

Question

What would be the query for the employee who worked for all the department. here department and employee have many to many cardinality.

The tables are:

CREATE TABLE employees
(
    employee_id int NOT NULL CONSTRAINT pk_employees PRIMARY KEY,
    employee_name nvarchar(128) NOT NULL CONSTRAINT uk_employees_employee_name UNIQUE
);

CREATE TABLE departments
(
    department_id int NOT NULL PRIMARY KEY,
    department_name nvarchar(128) NOT NULL CONSTRAINT uk_departments_department_name UNIQUE
);

CREATE TABLE department_employees
(
    department_id int NOT NULL CONSTRAINT fk_department_employees_departments REFERENCES departments(department_id),
    employee_id int NOT NULL CONSTRAINT fk_departement_employees_employees REFERENCES employees(employee_id),
    CONSTRAINT pk_deparment_employees PRIMARY KEY (department_id, employee_id)
)

Sample data:

INSERT INTO employees
VALUES (1, 'John Doe'), (2, 'Jane Doe'), (3, 'William Doe'), (4, 'Margaret Doe')

INSERT INTO departments
VALUES (1, 'Accounting'), (2, 'Humman Resources'), (3, 'Marketing')

INSERT INTO department_employees
VALUES 
    (1, 1), (2, 1), (3, 1),
    (2, 2), (2, 3),
    (3, 3), (3, 4)

Expected results:

+-------------+---------------+
| employee_id | employee_name |
+-------------+---------------+
|           1 | John Doe      |
+-------------+---------------+

Answer 1

This operation is called Relation Division: on Relational algebra.

It can be implemented in sql with a query like the following

SELECT *
FROM dbo.employees e
WHERE 
    NOT EXISTS (
        SELECT *
        FROM departments d
        WHERE d.department_id NOT IN (
            SELECT dp.department_id
            FROM department_employees dp
            WHERE dp.department_id = d.department_id AND dp.employee_id = e.employee_id
        )
    )

Notice that the query: "Give me the employees that work for all departments" is equivalent to "Give me all employees that there is no department that the employee is not working for"

Answer 2

You can try this below query.

Here in a variable all distinct count department has been taken from department master table. After that only those employee has been selected where count match with distinct linked department count in relation table.

declare @distinctDeptCount int 
SET @DistinctDeptCount = (SELECT Count(Distinct department_id) FROM departments)
--SELECT @DistinctDeptCount

SELECT Distinct employees.employee_id, employee_name
from employees     
where employees.employee_id in (
select employee_id from department_employees GROUP BY employee_id HAVING COUNT(department_id) = @distinctDeptCount
)

OR

SELECT Distinct employees.employee_id, employee_name
from employees     
where employees.employee_id in (
select employee_id from department_employees GROUP BY employee_id HAVING COUNT(department_id) = 
(SELECT Count(Distinct department_id) FROM departments)
)

The output is as shown below

employee_id employee_name
1           John Doe

Here is the live demo Emp. with all departments

Answer 3

You can try this

SELECT
    emp.name
FROM 
    Employee emp 
JOIN 
    EmpDept empd 
ON 
    emp.EmployeeID = empd.EmpId 
GROUP BY
    emp.EmployeeID
HAVING 
    count(emp.EmployeeID) = (select count(1) from Department)