Categories
Development

finding employees that are always together

I have a homework assignment on a company database with relations as follows:

Employee(eid,ename,salary,did,classification)  - primary key is eid
Project(pid,pname,did,budget,duedate)  - primary key is pid
Onproject(pid,eid,fdate) - primary key is (pid,eid)

There are more relations, but I don’t think they are relevant (department,budget).

I need to find and display workers id(eid) that are always doing the same projects(always together). I need to display every pair only once.

I’m trying this:

SELECT  
DISTINCT (e1.eid , e2.eid)

FROM
employee as e1 , employee as e2 

WHERE

e1.eid<>e2.eid  
AND e1.eid in 
(SELECT eid,pid as A from onproject where pid IN(
 (select pid as B from onproject where eid = e2.eid )INTERSECT (select pid as AB from onproject where eid = e1.eid)))
AND 
e2.eid in 
(SELECT eid,pid as C from onproject where pid IN
 ((select pid as D from onproject where eid = e1.eid) intersect (select pid as AC from onproject where eid = e2.eid)))

Leave a Reply

Your email address will not be published. Required fields are marked *