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/

Possible newbie SQL question. Help.

I have the below table

Organization:
emp_id, emp_name, manager_id, level

level 3 employees report to level 2 employees than report to level 1 employees

I need to select all level 3 employees, their direct manager (level 2) and indirect manager (level1).

Any help?Treat each level as a separate table:

select l3.emp_name, l2.emp_name, l1.emp_name
from emp l1, emp l2, emp l3
where l1.emp_id = l2.manager_id
and l2.emp_id = l3.manager_id;

Or in Oracle there is "CONNECT BY" to perform a tree-structured query.