1

This is my database:

  • Employee (id, department_id, name)
  • Department(id, name)
  • Project(id, departament_id)
  • Work (employee_id, project_id)

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!

The Impaler
  • 45,731
  • 9
  • 39
  • 76
4vol
  • 15
  • 7

2 Answers2

1

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

Barry Piccinni
  • 1,685
  • 12
  • 23
  • Excelent site, thanks! I have one more question: all joins are commutative in sql? – 4vol Aug 31 '18 at 14:11
  • Order does not matter for `INNER` joins. This is because the inner join looks for data which exists in both tables only. Order does matter for `OUTER` joins though, and your query will return different results. For more information, see the accepted answer here: https://stackoverflow.com/questions/9614922/does-the-join-order-matter-in-sql – Barry Piccinni Aug 31 '18 at 14:17
  • Hmm, your query is simple, but I want to see employees that are working in ALL projects of a certain department. Your query seem to return employees that are working in any project of a certain department – 4vol Aug 31 '18 at 14:19
  • @4vol I have edited my solution. The above works, but I suspect it can be simplified. I will continue to test this solution and see if I can improve it further. – Barry Piccinni Aug 31 '18 at 15:17
  • Piccini - Thanks, I've just tested your code and it works. I am curious whether is any other solution clearer and more elegant than that. – 4vol Aug 31 '18 at 15:31
0

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

randomDude1001
  • 169
  • 4