CODEWITHSUNDEEP
mysqlsqlsubquery
4vol
4vol

Reputation: 15

Employees working in all projects of a department

This is my database:

With PK, FK (for example: Employee has PK id and FK departament_id (id) from Department)

I want to select all the employees (E.name) that are working in all projects of a certain department (Development, for example).

This is my query:

 SELECT E.name, 
        COUNT(P.id) AS PW, 
        AVG(DISTINCT(Q.allproj)) AS PrjOfDep
   FROM Employee E 
   JOIN WORK W 
     ON W.employee_id= E.id
   JOIN Project P 
     ON P.id = W.project_id
   JOIN (SELECT D.id, 
                COUNT(P1.id) AS allproj 
           FROM Project P1
           JOIN Department D 
             ON P1.departament_id = D.id
            AND D.name = "Development" 
       GROUP BY D.id) Q ON P.departament_id = Q.id
       GROUP BY E.ID HAVING PW = PrjOfDep

This is kinda working, this is the result:

Name    PW  PrjOfDep    
Stark   2   2.0000
Mike    2   2.0000

I didn't know how to check if all the projects where Employee E is working include all the projects of departament "development", so I did a trick and my query looks very ugly. I counted all the projects where Employee E is working in Development and I compared that with the number of project of Departament "Development".

PrjOfDep is an average, I did this ugly trick because the query 

SELECT E.name, 
       COUNT(P.id) AS PW 
  FROM Employee E 
  JOIN WORK W 
    ON W.employee_id = E.id
  JOIN Project P 
    ON P.id= W.project_id
  JOIN (SELECT D.id, 
               COUNT(P1.id) AS allproj 
  FROM Project P1 
  JOIN Department D 
    ON P1.departament_id= D.id
   AND D.name = "Development" 
   GROUP BY D.id) Q 
    ON P.departament_id= Q.id
   GROUP BY E.id HAVING PW = Q.allproj

gives me the error:

 1054 - Unknown column 'Q.allproj' in 'having clause'

I want a more elegant query for my problem and I want to see only the name of the Employee. Thanks!

Upvotes: 1

Views: 1764

Answers (2)

randomDude1001
randomDude1001

Reputation: 169

This is my solution

SELECT
  EMP.*
FROM Employee EMP
JOIN Department DEP
ON 1 = 1
 AND DEP.did = EMP.did
WHERE 1 = 1
 AND DEP.Deptname = 'Development'
 AND EMP.eid NOT IN(    
  SELECT DISTINCT
    DEPEMP.eid
  FROM Employee DEPEMP
  JOIN Project PRO
  ON 1 = 1
   AND PRO.did = DEPEMP.did
  LEFT JOIN Work WRK
  ON 1 = 1
   AND WRK.eid = DEPEMP.eid
   AND WRK.pid = PRO.pid
  WHERE 1 = 1
   AND DEPEMP.did = DEP.did
   AND WRK.eid IS NULL
);

Sadly the solution is not that elegant. Try it out here

Upvotes: 0

Barry Piccinni
Barry Piccinni

Reputation: 1801

Your attempts seem to be trying to do an awful lot more than what you have asked. If your question is simply how to

select all the employees (E.name) that are working in all project of a certain department (Development, for example).

Then something like the following should work: 

SELECT e.name
FROM Employee e
INNER JOIN Work w ON e.eid = w.eid
INNER JOIN Project p ON w.pid = p.pid
INNER JOIN Department d ON p.did = d.did
WHERE d.Deptname = "Development"
GROUP BY e.name
HAVING  COUNT(p.Projname) = (SELECT count(p.Projname)
                FROM Department d
                INNER JOIN Project p ON d.did = p.did
                WHERE d.Deptname = "Development")

Demo of this: SQL Fiddle

Upvotes: 1

Related Questions