SQL Query: Cascading "Managers" query problem

I have a database of employees and their direct manager. Each employee row has a ManagerId column. If I run a report to see which employees report to "Bob", I get a list of employees with "Bob's" Id in the ManagerId column. This is fine for finding out who reports directly to Bob but I want to see everyone that reports to Bob or any of the directors, managers, or supervisors that report to Bob.

How do I do this?

Edit 1 - This is for SQL Server 2005 / 2008. I'd like 开发者_如何转开发all employees under Bob, not just those reporting directly to him.

Alternatively, in recent versions of SQL Server, you can accomplish this via a recursive Common Table Expression.

See this MSDN question and its answers. To quote:

WITH EmpCTE(empid, empname, mgrid, IsRoot)
AS
(

 -- Anchor Member (AM)
 SELECT empid, empname, mgrid, CASE WHEN mgrid IS NULL THEN 1 ELSE 0 END IsRoot
 FROM Employees
 WHERE empid = @empid

 UNION ALL

 -- Recursive Member (RM)
 SELECT M.empid, M.empname, M.mgrid, CASE WHEN M.mgrid IS NULL THEN 1 ELSE 0 END
 FROM Employees AS M
  JOIN EmpCTE AS E
   ON E.mgrid = M.empid
)
SELECT * FROM EmpCTE
GO

That'll give you a starting point.

You can do this by using hierarchical queries if your database supports it. Which Database are you using.

For Oracle : http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm

For SQL Server (and I believe the ANSI SQL Syntax is also supported for Oracle from 11g R2), check this link.

http://msdn.microsoft.com/en-us/library/ms186243.aspx