psj01 psj01 - 1 month ago 9
MySQL Question

select name of the emp who is in all projects by a certain department

enter image description here
enter image description here




how can i modify this query to show only the name of employee who is in all the projects by dpt 17.

so far i have :

select fname, lname from employee where ssn in
(
select distinct essn from works_on b where
b.pnum in ( select pnum from project where dnum = 17)

)


Here is the link to my sql fiddle i created [Link]

Really appreciate any help. I am trying to understand the logic behind how it works..

Answer

SQL is easy when you take it in steps. I suggest you run these queries and make sure you understand their results

First project data for department 17

 SELECT *
 FROM project
 WHERE project.dnum = 17

Now all the people that work on those

 SELECT *
 FROM project
 LEFT JOIN works_on ON project.pnumber = works_on.pno
 WHERE project.dnum = 17

How we want to know works_on.essn count that is equal to the total number of projects

 SELECT works_on.essn
 FROM project
 LEFT JOIN works_on ON project.pnumber = works_on.pno
 WHERE project.dnum = 17
 GROUP BY works_on.essn
 HAVING count(*) = (SELECT count(*) FROM project WHERE project.dnum = 17)

Easy right?

If a sub-query in having does not work you can do it with a cross join like this

 SELECT works_on.essn
 FROM project
 LEFT JOIN works_on ON project.pnumber = works_on.pno
 CROSS JOIN (SELECT count(*) AS C FROM project WHERE project.dnum = 17) SUB
 WHERE project.dnum = 17
 GROUP BY works_on.essn 
 HAVING count(*) = MAX(SUB.C)
Comments