Problem building query

Hi,
my problem is this :

I have a table like this
---------------------------------------------
Name EmployeeID SupervisorID
---------------------------------------------
Allen 2 3
Smith 3 2
Jack 4 3
Harrison 5 4

I want to retrieve the list of applicable supervisorIDs by passing current UserID - the result should not contain the currentUserID,UserID of his supervisor and the UserID of persons who are supervisors of his supervisor.

To make it clear , if i retrieve the list of supervisors that can be applied to Smith , the query should return only Allen and not Jack and harrison because they are lower in the Organisational hieararchy.

Pls help me in building the query..I tried out NOT IN ..but doesnt work..


Thanks in advance,
Sundaram Venu

Hi,

If you are using SQL 2005, you can use the CTE (Common Table Expressions) functions in order to use on hierarcial data

Eralper

http://www.kodyaz.com/articles/arti...px?articleid=18