CODEWITHSUNDEEP
sql
Hanif
Hanif

Reputation: 1

How do I find one Table

If I have one Table Employee where
EmployeeID,
EmployeeName,
FirstName,
LastName,
ManagerID,
ManagerName

here ManagerID have also EmployeeID.

Employee work under Manager.

How do I find from table Employee
FirstName,
EmployeeName,
ManagerName

where one manager have not more then 6 employee.

Upvotes: 0

Views: 69

Answers (2)

Moinul
Moinul

Reputation: 11

/* Considering there may be top level employees where manager id will be null. Code in MSSQL */ 

SELECT FirstName, EmployeeName, ManagerName 
FROM Employee e INNER JOIN 
(
    SELECT ManagerId 
    FROM Employee 
    WHERE ManagerId IS NOT NULL 
    GROUP BY ManagerId
    HAVING COUNT(*) <= 6  
) t  
ON (e.ManagerId = t.ManagerId OR e.ManagerId IS NULL);

/* If we don't care whether the manager id is null or not then */

SELECT FirstName, EmployeeName, ManagerName 
FROM Employee e INNER JOIN 
(
    SELECT ManagerId 
    FROM Employee 
    GROUP BY ManagerId
    HAVING COUNT(*) <= 6  
) t  
ON (e.ManagerId = t.ManagerId);

Upvotes: 0

Thomas G
Thomas G

Reputation: 10216

SELECT FirstName,EmployeeName, ManagerName
FROM Employee e
WHERE e.ManagerID IN
(
    SELECT ManagerID
    FROM Employee 
    GROUP BY ManagerID
    HAVING COUNT(*) <= 6

)

Upvotes: 1

Related Questions