AL-zami AL-zami - 1 year ago 110
MySQL Question

what should be the correct sql

I have the following schema:





i have to find the name of the projects where all the employees work more than 3 hours.There might be some cases where an employee works less then 3 hours but others does more than three hours.
I need the project name where all the employee works more than three hours. Can't figure out how to take that into consideration.

Please help me out ! thanks

Answer Source

When you rephrase your question it's find the projects where no employee works less than three hours, i.e. the minimum hours is greater than three:

SELECT p.pname
FROM project AS p
JOIN works_on AS w ON p.pnumber = w.pnumber
GROUP BY p.pname
HAVING MIN(hours) > 3

Or using NOT EXISTS:

FROM project AS p
   FROM works_on AS w
   WHERE p.pnumber = w.pnumber
     AND hours <= 3

Caution, this is not exactly the same, as it will return projects without assigned employees.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download