Possible self-join question

Suppose I have a table of employees, and who each one reports to, like so:
EmployeeID ManagerID EmployeeName
1 Caroline
2 1 Teresa
3 2 John
4 2 Brent
5 3 Sherry
6 5 Rudy
Ok, so what I need to happen is, suppose I run a query on Employee #2,
Teresa Switzer. What I need to see are all employees that ultimately report
up to her (her employees, her employees' employees, and so on). So in my
results I should see John, Brent, Sherry, and Rudy, since all those employee
report to someone who eventually reports to Teresa. If I were to query on
Employee #3, John Maxwell, I'd see just Sherry and Rudy. Does anyone have an
y
ideas how to make this happen?
Many thanks for any suggestions.In SQL 2005 use a Common Table Expression (there are several good examples i
n
Books Online) , for SQL 2000 take a look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
ML
http://milambda.blogspot.com/|||ML - you rock. I think this example does exactly what I need. Thanks so much
for the help.
"ML" wrote:

> In SQL 2005 use a Common Table Expression (there are several good examples
in
> Books Online) , for SQL 2000 take a look at this example:
> http://milambda.blogspot.com/2005/0...or-monkeys.html
>
> ML
> --
> http://milambda.blogspot.com/|||You make me blush. :)
ML
http://milambda.blogspot.com/