Join table with a subquery SUM function in mySQL-CodePudding

Here's the prompt and the code I'm using. "FIND THE NAME OF THE FEMALE EMPLOYEES WHOSE WORKING HOURS FOR ALL PROJECTS IS MORE THAN 20 HOURS"

SELECT employee.ssn, employee.first_name, employee.last_name
FROM employee
WHERE employee.sex='F' AND employee.ssn IN (
    SELECT working_hour.ssn
    FROM working_hour
    GROUP BY working_hour.ssn
    HAVING SUM(working_hour.work_hour) > 20)
ORDER BY employee.first_name, employee.last_name;

Now I want the output to include the SUM(working_hour.work_hour) grouped by the the employee.ssn but I can't seem to find the correct cote to excecute this.

PS: Does it have something to do with the working_hour table having a composite key?

CodePudding user response：

If you want to access the fields in the subquery, then I would rephrase this using a join:

SELECT e.ssn, e.first_name, e.last_name, wh.total_hours
FROM employee e
INNER JOIN
(
    SELECT ssn, SUM(work_hour) AS total_hours
    FROM working_hour
    GROUP BY ssn
    HAVING SUM(work_hour) > 20
) wh
    ON wh.ssn = e.ssn
WHERE e.sex = 'F'
ORDER BY e.first_name, e.last_name;