mysql select 多条件查询_mysql – 具有多个条件的SQL SELECT查询

我坚持使用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

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

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'

到目前为止一切都很好……但现在我想要所有员工,他们的任务是“编程”和“支持”.这个查询给了我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'

我通过此查询收到三条记录

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约翰和1x迈克.但这不是我想要的.我希望所有拥有“编程”和“支持”任务的员工 – 而不是那些只有其中一项任务的员工.

还有另一种选择.我用子查询使用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')

但我收到这个查询NULL,虽然有一个员工,谁有两个任务：约翰！

我该如何实现这样的查询？

最好的祝福

基督教