SQL Query problem

Question

Consider two table.Employee and Project.Employee table has fields like eid,ename.Project table has fields like pid,pname.Now,since an employee can work on many projects and a project can be done by many employees, therefore,as evident,there is a many to many relationship b/w the two tables.Break the many to many,and create a new table called Assignment which consists as foreign keys the eid and pid.

Here is the question, I Want to find out those employee pairs who have worked with each other in more than 1 project.For example if emp A and emp B have worked with each other on more than one project then they qualify for the above query result.

Kindly let me know what will be the query for the mentioned problem.

Answer 1

Here's a pretty common and straightforward solution:

SELECT a1.eid, a2.eid
FROM Assignment a1
JOIN Assignment a2 ON (a1.eid < a2.eid AND a1.pid = a2.pid)
GROUP BY a1.eid, a2.eid
HAVING COUNT(*) > 1;

This query does a join to match rows in the Assignment table to other rows in the same table with the same project and a different employee. We use < to compare the employee id's so we don't get duplicate pairings.

Then we use GROUP BY to make sure there's only one row for each pair of employees.

The HAVING clause picks only those groups that have multiple rows, which are the pairs of employees who have worked on multiple projects together.

Answer 2

This can help you?

select ass1.eid, ass2.eid 
from Assignment ass1         
join Assignment ass2 on ass1.pid = ass2.pid
where ass1.eid < ass2.eid
group by ass1.eid, ass2.eid
having COUNT(0) > 1

Answer 3

This is how I would write it, very similar to what Bill wrote with the difference being on counting the distinct projects in case there is no unique constrain on (emp_id, prj_id) in the relation table:

SELECT r1.emp_id, r2.emp_id, COUNT(DISTINCT r1.prj_id) cntProjects
  FROM r_emp_prj r1, r_emp_prj r2
 WHERE r1.emp_id < r2.emp_id
   AND r2.prj_id = r1.prj_id
 GROUP BY r1.emp_id, r2.emp_id HAVING COUNT(DISTINCT r1.prj_id) > 1

If the relation table also stores the role(dev, lead, q&a etc.) of the person in the project, it is possible to have multiple entries in the relation table for the same (emp_id, prj_id) pair.

---

This retrieves the names as well:

SELECT r1.emp_id, emp1.name,
       r2.emp_id, emp2.name,
       COUNT(DISTINCT r1.prj_id) cntProjects
  FROM r_emp_prj r1, r_emp_prj r2,
       emp emp1, emp emp2
 WHERE r1.emp_id < r2.emp_id
   AND r2.prj_id = r1.prj_id
   AND emp1.id = r1.emp_id
   AND emp2.id = r2.emp_id
 GROUP BY r1.emp_id, emp1.name, r2.emp_id, emp2.name
HAVING COUNT(DISTINCT r1.prj_id) > 1