Querying a table and returning a heirarchy

Posted on 2007-10-10
Last Modified: 2013-12-07
Business Goal: Management has asked us to return the listing of everyone name who is currently reports to them and everyone under them and so and so forth.

Currently, we have an Oracle database containing a table named as PA_STUDENT. This PA_STUDENT table contains all the employees who work in our organization in column A and the name of their supervisor in column B.

What I need to get from PA_STUDENT is for example:

If I query the name of the Director, then I should return all the directors subordinates and the subordinates' subordinates. If the Director had Joe, Michael, and Dave working under him as group leaders, and Joe, Michael, and Dave have 6 people working under of them, and those 6 people had more people working under each of them and so and so forth, I would like to see all those names come up.

I remember that when I used to take C/C++ classes, we learned the concept of "binary search" trees where 1 parent has 2 child nodes. In this case, we have parents who have multiple children (the supervisors direct reports) and those children also have multiple children.

I hope this makes sense. How can I get this accomplished assuming that I have PA_STUDENT as the table using SQL to grab this information? Please advise.
Question by:piyushdabomb
    LVL 18

    Expert Comment

    LVL 18

    Expert Comment

    another good one

    You could find so many reulsts if you do a little search on EE itself.

    Please let me know if you are looking for anythig specific

    Author Comment

    From the link above, it looks like this is PL/SQL; is this true? Are there any alternatives to connecting without using PL/SQL?
    LVL 18

    Expert Comment

    You could do it in SQL using Connect by prior clause.

    Not neccessary to use PL/SQL
    LVL 18

    Expert Comment

    in your case the query would be

    select lpad(' ',2*(level-1)) || to_char(column_a)  
      from pa_student
      start with parent is null
      connect by prior column_a = column_b

    Author Comment


    Thank you for your advise so far. May I request you to provide an explanation to your code?

    If the supervisor is in column B and the user is in column A, this is the pseudocode to eventually satisfy the SQL command:

    Note: Instead of working with names, I need to work with a Universal ID. Each name has a unique identifier which is the Universal ID (UPI #)

    Look for the UPI # in column B and extract all the UPI #'s in column B
    Then take each extracted UPI # and then go back to column B and look for the corresponding rows.


    DirectorA - UPI #: 1111
    SubordinateA (child of Subordinate DirectorA) - UPI# 2_1111
    SubordinateB (child of Subordinate DirectorA) - UPI# 2_2222
    Subordinate AA (child of SubordinateA) UPI #: 3_1111
    Subordinate AB (child of SubordinateA) UPI# 3_2222
    Subordinate BA (child or SubordinateB) UPI#: 1233_1
    Subordinate BB (child or SubordinateB) UPI#: 1233_2
    Subordinate BC (child or SubordinateB) UPI#: 1233_3
    Subordinate BAA (child or SubordinateBA) UPI#: 1233_1_1111

    The result should get me all the values in a tree form. In SQL, since they come out as a table, that is fine.

    Note: I don't know how many levels deep this can go but I don't think it should matter.

    LVL 27

    Accepted Solution

    You can use hierarchical queries in Oracle. See the example below.
    This query traverses from an employee, down all the way to the subordinates. The column level indicates the depth of the tree.

    SQL> select empno, ename, mgr  from emp;

         EMPNO ENAME             MGR
    ---------- ---------- ----------
          7369 SMITH            7902
          7499 ALLEN            7698
          7521 WARD             7698
          7566 JONES            7839
          7654 MARTIN           7698
          7698 BLAKE            7839
          7782 CLARK            7839
          7788 SCOTT            7566
          7839 KING
          7844 TURNER           7698
          7876 ADAMS            7788
          7900 JAMES            7698
          7902 FORD             7566
          7934 MILLER           7782

    14 rows selected.

    SQL> select empno, mgr, ename, prior ename, level
       from emp
       start with empno = 7839
       connect by prior empno = mgr

    ---------- ---------- ---------- ---------- ----------
          7839                 KING                           1
          7566       7839 JONES      KING                2
          7788       7566 SCOTT      JONES               3
          7876       7788 ADAMS      SCOTT               4
          7902       7566 FORD       JONES               3
          7369       7902 SMITH      FORD                4
          7698       7839 BLAKE      KING                2
          7499       7698 ALLEN      BLAKE               3
          7521       7698 WARD       BLAKE               3
          7654       7698 MARTIN     BLAKE               3
          7844       7698 TURNER     BLAKE               3
          7900       7698 JAMES      BLAKE               3
          7782       7839 CLARK      KING                2
          7934       7782 MILLER     CLARK               3

    14 rows selected.


    Author Comment

    Thanks for everyones suggestion however, there are problems with this.

    Although the connect by prior seems to work using Oracle Toad, I need to implement the exact same code snippets shown above into Excel for Visual Basic for applications. Using MS Query, connect by prior is not an applicable query. How else can I make this work? Can I somehow include the connect by prior into my VBA code? Please advise.
    LVL 27

    Expert Comment

    "Connect by proior" is an oracle syntax. As long as you can run an ORACLE query from anywhere, it should work. I am not sure about the limitations you have mentioned.
    If your interface has syntax limitations, have this query as a stored procedure in your database and invoke the same.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now