Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    29

    Unanswered: construct hierarchy

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Code:
    select emp_no
    from emp
    where mgr_no = &&par_emp_no
    union
    select mgr_no 
    from emp
    where mgr_no >= &&par_emp_no
    order by 1 desc;
    "&&", 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).

  3. #3
    Join Date
    Feb 2005
    Posts
    29
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    Last edited by r937; 03-04-05 at 14:25.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •