Trouble with hierarchical query

From: Vince <vfabro_at_covansys.com>
Date: 19 Jul 2001 11:20:36 -0700
Message-ID: <28367dfb.0107191020.4dfcca4e@posting.google.com>

I'm having a tough time querying hierarchical data and returning a result set in the format I want. Consider the following data (taken from Oracle's documentation):
ORG_CHART EMPNO MGR JOB
------------ ---------- ---------- ---------

KING               7839            PRESIDENT
  JONES            7566       7839 MANAGER
    SCOTT          7788       7566 ANALYST
      ADAMS        7876       7788 CLERK
    FORD           7902       7566 ANALYST
      SMITH        7369       7902 CLERK
  BLAKE            7698       7839 MANAGER
    ALLEN          7499       7698 SALESMAN
    WARD           7521       7698 SALESMAN
    MARTIN         7654       7698 SALESMAN
    TURNER         7844       7698 SALESMAN
      JAMES        7900       7698 CLERK
 CLARK             7782       7839 MANAGER
   MILLER          7934       7782 CLERK

I would like to be able to query for a result set that includes 2 columns: the ancestor from my IN clause (level = 1), and the descendant (level > 1). For example, I would like a single query where I can specify a where condition to retrieve the children of Jones and Clark (such as "emp_no in (7566, 7782)"). It should return the following result set:
7566 7566
7566 7788
7566 7876
7566 7902
7566 7369
7782 7782
7782 7934

I can easily get a list of any one ancestor and his children, but I can't get a list of multiple ancestors and their children.

Any ideas? Help!

Thanks,
Vince Received on Thu Jul 19 2001 - 13:20:36 CDT