Now we have 3 tables which are employees, workson, project.

For the employees table (with sample data)

For the workson table we have

For the projects table

Based on the dataset, it should be clear that the only employees who worked together on more than 2 projects are Jim and Sam. Hence that should be the expected outcome which is 2.

My own code however seems to return the number of projects that each employee had worked in and retrieved rows of 3000 (every single employee). When the output should only be a simple integer.

SELECT COUNT(e.employeeid)
FROM employees e
JOIN workson w ON e.employeeid = w.employeeid
GROUP BY e.employeeid
HAVING COUNT(w.projectid) > 9 ;

CodePudding user response：

You need to join the workson with itself like so:

select e1.employeeid, e2.employeeid, count(e1.projectid) as worked_together
from workson as e1
join workson as e2 on e1.projectid = e2.projectid and e1.employeeid < e2.employeeid
group by e1.employeeid, e2.employeeid
order by worked_together desc

The < condition ensures that rows such as (Jim, Jim, Evil) and the opposite pairs such as (Jim, Sam, Evil) <-> (Sam, Jim, Evil) are not included.

The result contains pairs of employees and the count of projects where they worked together. It is trivial to add a having clause.

CodePudding user response：

You need to join the table workson with itself:

with u as 
(select w1.employeeid e1, w2.employeeid e2, count(*) ct
from workson w1 inner join workson w2
on w1.projectid = w2.projectid
and w1.employeeid < w2.employeeid
group by w1.employeeid, w2.employeeid
having count(*) > 2),
v as
(select distinct e1 from u union select distinct e2 from u)
select count(*) from v;

Fiddle