I have an emp table with the two columns
emp_no and mgr_no

say for e.g
emp_no mgr_no
--------------------
10 100
15 90
20 100
25 150
30 100
90 200
100 200
150 200
200 300
300 400

I want to construct a query for empno = 100, which will construct hieachy both above and one level below.... e.g
400
300
200
100
30
20
10

Here 10,20, and 30 are directly below Mgr 100

For emp_no 200 the output should be
400
300
200
150
100
90
Here emp - 100,90 and 150 report to Mgr - 200


Any suggestions/comments ?

Many Thanks.

Ash

"&&", in Oracle, requires you to insert value for a parameter "par_emp_no". If you use another DB, see if it needs to be changed.
Also, I'd say that your first example lacks in mgr_no = 150 (which is higher than the parametrized 100).

Thanks for the suggestion. This works as per the example I had given.

However it is not necesssary that the manager's empno is greater than his employee.

ash, if you go all the way up the tree from any given level, what is the maximum dpth of the tree?

also, if you just dump them out in one column, like this --

400
300
200
150
100
90

what possible use could this be? how do you know which one's the boss, which one's the boss's boss, which one's the subordinate?