SQL query for finding pairs that share the same set of values

Question

I have table Tasks(employee_name, task)

------------------
Joe | taskA
Joe | taskB
Ted | taskA
Jim | taskB
Ray | taskA
Ray | taskB
John| taskA
Tim | taskC

I need to find all pairs of employees that have the same set of tasks.

For example using the data above the result set should be:

---------------------
employee1 | employee2
---------------------
  Joe     | Ray
  Ted     | John

I'm using MySQL for the database.

Fant

Okay, this is just a wild suggestion, but maybe you could try a join between the table and itself, as though it were another table? – Informality

Answer 1

select a.employee_name,b.employee_name
from tasks as a, tasks as b
where a.employee_name>b.employee_name
group by a.employee_name,b.employee_name
having group_concat(distinct a.task order by a.task)=group_concat(distinct b.task order by b.task)

Marbles

+1 If you move the GROUP_CONCAT() terms into a HAVING clause. You can't do grouping functions in a WHERE clause. – Hennery

That's awesome! Never knew about the group_concat() function. That's exactly where I was getting stuck. Thanks! :) – Fant

Answer 2

Join the table to itself, pick one employee_name to be greater than the other, and where the tasks are equal.

select emp1.employee_name, emp2.employee_name, emp1.task
from tasks emp1
inner join task emp2
on emp1.employee_name > emp2.employee_name
and emp1.task = emp2.task

Hopefully you have a REAL PK, or this is just a sample exercise. This would not be good in a production environment since employee_name is not going to uniquely identify an employee in most companies/systems.

Driskell

This only says that two employees share at least one common task. The requirements are that all the tasks are equivalent. – Sinistrous