CODEWITHSUNDEEP
sqlsql-server-2008foreign-keysrecursive-query
M M
M M

Reputation: 23

SQL Query with recursive foreign key

I have a table with columns EmployeeID, EmployeeName, and ManagerID. ManagerID is a recursive FK of EmployeeID. I am trying to query the table so that the name of employees who are managers are provided. My train of thought is that an Employee would be a Manager if their EmployeeID is also a ManagerID. I set the ManagerID to be NOT NULL because that person would be the manager's manager. When I execute the query a blank manager column is returned.

SELECT EmployeeName AS Manager
FROM Employee E
WHERE E.EmployeeID=E.ManagerID AND
E.ManagerID <> null

Upvotes: 1

Views: 3911

Answers (3)

virender sihag
virender sihag

Reputation: 1

WITH allfkey AS
(

    SELECT referenced_object_id,object_name(referenced_object_id) [object_name],name
    from sys.foreign_keys where parent_object_id=object_id('employee') -- put your table name here

    UNION ALL

   SELECT f.referenced_object_id,object_name(f.referenced_object_id) [object_name],f.name
    from sys.foreign_keys f join allfkey on f.parent_object_id=allfkey.referenced_object_id

)
SELECT * from allfkey

Upvotes: -1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

SELECT EmployeeName AS Manager
FROM Employee 
WHERE EmployeeID IN 
      ( SELECT ManagerID 
        FROM Employee
        WHERE ManagerID IS NOT NULL
      )
  AND ManagerID IS NOT NULL

or

SELECT EmployeeName AS Manager                --- show name 
FROM Employee M                               --- of any employee
WHERE EXISTS                                  --- for whom exists
      ( SELECT *                              --- at least one
        FROM Employee E                       --- employee
        WHERE M.EmployeeID = E.ManagerID      --- that is under his management
      )
  AND ManagerID IS NOT NULL                   --- and is not the "root" manager

Upvotes: 1

Bohemian
Bohemian

Reputation: 425218

I noticed from a comment that you only want employees that are first-line managers. Therefore, you can do a further join to assert that there are no further sub-employees:

SELECT distinct M.*
FROM Employee m
join Employee e on e.managerid = m.employeeid
left join Employee s on s.managerid = e.employeeid
WHERE s.employeeid is null;

Upvotes: 1

Related Questions