Creating Correlated Sub query using Count

Transact-SQL
1

I need to create a correlated sub query which shows the Employee ID, Employee_First_Name and the employees who have 2 or more tasks. But Ive got into a muddle and don't know how to write it correctly. The employee_id is in the employee table and task table, allowing them to join.

So far I've done this:

SELECT E.EMPLOYEE_ID, E.FIRST_NAME

FROM EMPLOYEE E

WHERE TASK.EMPLOYEE_ID IN (SELECT COUNT(TASK.EMPLOYEE_ID) FROM TASK)

HAVING COUNT (TASK.EMPLOYEE_ID) >=2;
2

Try this:

SELECT E.EMPLOYEE_ID, E.FIRST_NAME

FROM EMPLOYEE E

INNER JOIN (
    SELECT EMPLOYEE_ID
    FROM TASK
    GROUP BY EMPLOYEE_ID
    HAVING COUNT(*) >= 2
) AS T ON T.EMPLOYEE_ID = E.EMPLOYEE_ID

--ORDER BY E.ID
3

It's telling me I'm missing a keyword

4

Are you using Microsoft SQL Server? or are you on MySQL

5

Oracle SQL

6

this forum is for Microsoft SQL Server. But there might be folks that could help you

7

hi

dont know if this will work
please try .. thanks

:slight_smile:

:slight_smile:

SQL 
SELECT E.employee_id, 
       E.first_name 
FROM   employee E 
       JOIN task f 
         ON e.employee_id = f.employee_id 
            AND Count(f.employee_id) > = 2
8

Oracle requires the ';' at the end, but I'm assuming you added that.

Other than that, I don't see anything wrong with the syntax,