Trouble with hierarchical query

From: Vince <vfabro_at_covansys.com>
Date: 19 Jul 2001 09:44:24 -0700
Message-ID: <28367dfb.0107190844.6cb83b42@posting.google.com>

I am having trouble querying some hierarchical data and returning results in a particular way. Let's say I have the following data
(straight 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 query that table and retrieve two columns: an ancestor
(always level 1), and one of his descendants (level > 1). For
example, I would like to query the table for Jones and Blake (i.e. "start with empno in (7566, 7698)") and return the following result set:
7566 (Jones) 7566
7566 7788
7566 7876
7566 7902
7566 7369
7698 (Blake) 7698
7698 7499
7698 7521
7698 7654
7698 7844
7698 7900

I can't seem to get those two columns side-by-side.

Any ideas? Help!

Thanks,
Vince Received on Thu Jul 19 2001 - 11:44:24 CDT