SQL SELECT query with multiple conditions

Question

I am stuck in an SQL query. Let's say we have an employee, a task table, and many, many relationships between them. The tables look like this:

employees id|name 1 | John 2 | Peter 3 | Mike tasks id | name 1 | Support 2 | Programming 3 | Call customers 4 | Write Newsletters 5 | Write Invoices employees_tasks employee_id | task_id 1 | 1 1 | 2 2 | 3 2 | 4 2 | 5 3 | 2 

Now I want to get all the employees who have "Programming" as their tasks. Correct request:

 SELECT employees.id, employees.name FROM employees INNER JOIN employees_tasks ON employees.id = employees_tasks.employee_id INNER JOIN tasks ON employees_tasks.task_id = tasks.id WHERE tasks.name LIKE 'Programming' 

So far so good ... But now I want to get all the employees whose tasks are "Programming" and "Support". This query gives me NULL:

 SELECT employees.id, employees.name FROM employees INNER JOIN employees_tasks ON employees.id = employees_tasks.employee_id INNER JOIN tasks ON employees_tasks.task_id = tasks.id WHERE tasks.name LIKE 'Programming' AND tasks.name LIKE 'Support' 

I get three records with this query

 SELECT employees.id, employees.name FROM employees INNER JOIN employees_tasks ON employees.id = employees_tasks.employee_id INNER JOIN tasks ON employees_tasks.task_id = tasks.id WHERE tasks.name IN ('Programming', 'Support') 

2x John and 1x Mike. But that is not what I want. I want all employees who have tasks "Programming" and "Support" - not those who only have one of the tasks.

There is one more option. I am using ALL with a subquery. Here we go:

 SELECT employees.id, employees.name FROM employees INNER JOIN employees_tasks ON employees.id = employees_tasks.employee_id INNER JOIN tasks ON employees_tasks.task_id = tasks.id WHERE tasks.name = ALL (SELECT DISTINCT name FROM tasks WHERE name LIKE 'Programming' OR name LIKE 'Support') 

But I get NULL with this request, although there is an employee who has both tasks: John!

How can I implement such a request?

Best regards Christian

Answer 1

You need to join employees_tasks with your query a second time:

 SELECT employees.id, employees.name FROM employees INNER JOIN employees_tasks AS et1 ON employees.id = et1.employee_id INNER JOIN employees_tasks AS et2 ON employees.id = et2.employee_id INNER JOIN tasks AS t1 ON et1.task_id = t1.id AND t1.name = 'Programming' INNER JOIN tasks AS t2 ON et2.task_id = t2.id AND t2.name = 'Support' 

---

UPDATE

Alternatively, if you filter results only for tasks of interest, you can GROUP BY employee and return only those who have a given task counter:

 SELECT employees.id, employees.name FROM employees INNER JOIN employees_tasks ON employees_tasks.employee_id = employees.id INNER JOIN tasks ON employees_tasks.task_id = tasks.id WHERE tasks.name IN ('Programming', 'Support') GROUP BY employees.id, employees.name HAVING COUNT(DISTINCT tasks.id) = 2