CODEWITHSUNDEEP
mysqlsql
Sai Peri
Sai Peri

Reputation: 349

SQL iterative loop to see if employee works on all projects

The query I am supposed to form has to accomplish the following task:

Retrieve the names of all employees who work on every project.

I currently have three tables. The Employee, works_on, and project tables. The goal to accomplish this query is to get each project id from the project table, compare it to the project id in the works_on table. When there is a match it will get the SSN and get the names from the employee table. The query I have formed is this:

SELECT e.Fname, e.Minit, e.Lname, p.Pname 
FROM EMPLOYEE e, PROJECT p, WORKS_ON w 
WHERE p.Pnumber=w.Pno AND w.Essn=e.Ssn

But this outputs All the employees that work on each project not all the employees that work on EVERY project. Is there some way to iterate through a list of results from the query SELECT Pnumber FROM PROJECT?

I really hope I worded this question clearly for your understanding.

Upvotes: 2

Views: 1794

Answers (2)

Amaal R
Amaal R

Reputation: 1

SELECT e.Fname, e.Minit, e.Lname
FROM EMPLOYEE e
WHERE NOT EXISTS(SELECT PNum
                 FROM PROJECT
                 WHERE NOT EXISTS(SELECT *
                                  FROM WORKS_ON
                                  WHERE PNum=PNo AND Essn=e.ssn));

You can select the employee on the condition that:

  • There doesn't exist a project where the employee doesn't work on it.

You can use the innermost nested query to select tuples where there doesn't exist a WORKS_ON tuple where employee with Ssn works on project with Pnum. Then use the outermost nested query to select the tuples where the above condition doesn't hold ^^ (so there is an employee with Ssn that works on project with Pnum) for ALL projects.

I hope that makes sense and good luck!

Upvotes: 0

danblack
danblack

Reputation: 14701

Also you don't need PROJECT, WORKS_ON is sufficient.

HAVING filters the results after a GROUP BY.

The GROUP BY e.Ssn means that the COUNT(*) in HAVING is per employee. The JOIN ON WORKS_ON is mapping the user to PROJECT giving the count.

Use JOIN table tbl ON .. = tbl.id JOIN syntax - easier to read. 

SELECT e.Fname, e.Minit, e.Lname
FROM EMPLOYEE e
JOIN WORKS_ON w
  ON w.Essn=e.Ssn
GROUP BY e.Ssn
HAVING COUNT(*) = (SELECT COUNT(*) FROM PROJECTS)

Upvotes: 5

Related Questions