I need to join one table and one subquery

Question

SELECT * FROM Employee ( SELECT eid FROM Assignment GROUP BY eid HAVING SUM(hours_per_week) > 40 ) 

This is my code for finding hours_per_week, which is over 40. The subquery returns the eid of people with more than 40 hours. My question is how to display all people in Employee with the eid obtained from the subquery. Where? or join?

Answer 1

Use WHERE ... IN (SELECT ...)

 SELECT col1, col2, ..., coln FROM Employee WHERE eid IN ( SELECT eid FROM Assignment GROUP BY eid HAVING SUM(hours_per_week) > 40 ) 

Answer 2

 SELECT e.* FROM Employee AS e INNER JOIN ( SELECT eid FROM Assignment GROUP BY eid HAVING SUM(hours_per_week) > 40 ) AS ot ON ot.eid = e.eid 

Answer 3

 Select E.EID, sum(A.hours_per_Week) FROM employee E INNER join Assignment A on E.EID = A.EID Group By E.EID Having sum(A.Hours_per_week) > 40 

Why do you need a subquery?