AN SQL question

I have a table called tbl_users.
The columns in the table are: employeeID, name, dept, supervisorID [employeeID, dept and supervisorID are numbers]

-supervisorID is linked to employeeID; supervisorID is actually an
employeeID in the same table.

-ex: the supervisorID in Kevin's record is 23454, which is Linda's
employeeID, ie Linda is Kevin's supervisor, yet Linda is an employee herself who also has a supervisor.

I need an SQL query where all employees would be displayed that report to a certain suprevisor, AND if any of those employees are supervisors, the query should also display any of the employees under them, and so on until it has cascaded to the bottom, so to speak.

Example:
If Tom enters his supervisorID (73676) as the parameter to the query (I'm using Cognos Impromptu, for those curious), he would see all employees that report to him, including Linda. He would also see all the employees that report to Linda, including Kevin. He would also see all the employees that report to Kevin, etc.

I have come up with something like this, but it only gives me a couple of levels:

    select * from tbl_users where supervisorID in (select employeeID from tbl_users where supervisorID = 73676)

The dilemma is that I can't possibly know at which level the supervisor is at. He may have 2 levels that report to him or 10.

Is there another way to build this query to get all the employees under a supervisor cascaded to the very bottom?

Thanks!

Kevin Received on Mon Feb 25 2002 - 14:13:38 CET