3

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.

philipxy
  • 14,867
  • 6
  • 39
  • 83
hebime
  • 577
  • 1
  • 5
  • 15
  • 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? – Ariane Jul 11 '13 at 19:36

2 Answers2

6
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)
noz
  • 1,863
  • 13
  • 14
  • 2
    +1 If you move the GROUP_CONCAT() terms into a HAVING clause. You can't do grouping functions in a WHERE clause. – Bill Karwin Jul 11 '13 at 19:45
  • That's awesome! Never knew about the group_concat() function. That's exactly where I was getting stuck. Thanks! :) – hebime Jul 11 '13 at 20:07
0

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.

Bill Gregg
  • 7,067
  • 2
  • 22
  • 39
  • This only says that two employees share at least one common task. The requirements are that all the tasks are equivalent. – fuxes Mar 09 '19 at 14:34