I need to join one table and one sub-query

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 that is over 40. The sub-query returns the eid's of the people with more than 40 hours. My question is how would I display all of the people in Employee with the eid i got from the sub-query. A where? or a 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