CODEWITHSUNDEEP
sql
alexdotcom
alexdotcom

Reputation: 61

I need to join one table and one sub-query

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?

Upvotes: 4

Views: 58

Answers (3)

xQbert
xQbert

Reputation: 35333

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 the subquery?

Upvotes: 0

Adam Wenger
Adam Wenger

Reputation: 17560

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

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 838696

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
)

Upvotes: 6

Related Questions