MySQL 具有多个条件的 SQL SELECT 查询

SQL SELECT query with multiple conditions

提问by Christian

I'm stuck with a SQL query. Let's say we have an employee, a task table and a many to many association between them. The tables look like this:

我被 SQL 查询困住了。假设我们有一个员工、一个任务表以及它们之间的多对多关联。表格如下所示：

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 employees, who have "Programming" as their tasks. The correct query is:

现在我想让所有以“编程”为任务的员工。正确的查询是：

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 employees, whose tasks are "Programming" and"Support". This query gives me NULL:

到目前为止一切顺利……但现在我想得到所有员工，他们的任务是“编程”和“支持”。这个查询给了我 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 receive 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's not what I want. I want all employees, who have the tasks "Programming" AND "Support" - not those, who only have one of the tasks.

2x 约翰和 1x 迈克。但这不是我想要的。我想要所有拥有“编程”和“支持”任务的员工——而不是那些只有其中一项任务的员工。

There's another option. I use ALL with a subquery. Here we go:

还有另一种选择。我将 ALL 与子查询一起使用。开始了：

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 receive with this query NULL, although there is an employee, who have both tasks: John!

但是我收到了这个查询 NULL，虽然有一个员工，他有两个任务：约翰！

How can I implement such a query?

我怎样才能实现这样的查询？

Best Regards Christian

最好的问候基督徒

回答by eggyal

You need to join employees_tasksto your query a second time:

您需要再次加入employees_tasks您的查询：

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 your results for only the tasks of interest, you can GROUP BYemployee and only return those who have the desired task count:

或者，如果您只过滤感兴趣的任务的结果，您可以GROUP BY雇佣并只返回那些具有所需任务数的人：

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