Querying a table and returning a heirarchy

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.
Who is Participating?
SujithData ArchitectCommented:
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.

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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

piyushdabombAuthor Commented:
From the link above, it looks like this is PL/SQL; is this true? Are there any alternatives to connecting without using PL/SQL?
You could do it in SQL using Connect by prior clause.

Not neccessary to use PL/SQL
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
piyushdabombAuthor Commented:

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.

piyushdabombAuthor Commented:
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.
SujithData ArchitectCommented:
"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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.