SQL SELECT query with multiple conditions

Question

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:

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:

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.

There's another option. I use 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 receive with this query NULL, although there is an employee, who have both tasks: John!

How can I implement such a query?

Best Regards Christian

eggyal · Accepted Answer · 2012-05-10 11:56:20Z

7

You need to join employees_tasks to 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 your results for only the tasks of interest, you can GROUP BY employee and only return those who have the desired task count:

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

edited May 10, 2012 at 11:56

answered May 10, 2012 at 11:45

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Christian Over a year ago

Thanks. This works for two conditions. How can I handle it, if I have arbitrary conditions? Do I have to join the table for each condition?

eggyal Over a year ago

@Christian: See alternative, more scalable approach in my edited answer above.

Christian Over a year ago

I experimented with the "group by" and "having" with "count", too. But I didn't get it to work. Thank you very much!

Collectives™ on Stack Overflow

SQL SELECT query with multiple conditions

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related