Traversing Upward Through the Tree

You can start at a child and traverse upward by switching child and parent columns in the CONNECT BY PRIOR clause.


CREATE TABLE employees
( employee_id          number(10)      not null,
  last_name            varchar2(50)      not null,
  job_id               varchar2(30),
  department_id        number(10),
  salary               number(6),
  manager_id           number(6)
);
insert into employees( employee_id, last_name, job_id, salary,department_id ,manager_id)
values(                1001,        'Lawson',  'MGR',  30000, 1,             1004);
insert into employees( employee_id, last_name, job_id, salary, department_id ,manager_id)
values(                1002,        'Wells',   'DBA',  20000, 2,             1005 );
insert into employees( employee_id, last_name, job_id, salary, department_id ,manager_id)
values(                1003,        'Bliss',   'PROG', 24000, 3,             1004);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values(                1004,        'Kyte',    'MGR',  25000 ,4,             1005);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values(                1005,        'Viper',   'PROG', 20000, 1,             1006);
insert into employees( employee_id, last_name, job_id, salary, department_id,manager_id)
values(                1006,        'Beck',    'PROG', 20000, 2,             null);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values(                1007,        'Java',    'PROG', 20000, 3,             1006);
insert into employees( employee_id, last_name, job_id, salary, department_id, manager_id)
values(                1008,        'Oracle',  'DBA',  20000, 4,             1006);



SQL> SELECT LEVEL, LPAD(' ', 2 * LEVEL - 1) || last_name AS employee
  2  FROM employees
  3  START WITH last_name = 'Kyte'
  4  CONNECT BY PRIOR manager_id = employee_id;

     LEVEL        EMPLOYEE
------------------------------------------------------------------------
         1 Kyte

         2   Viper

         3     Beck


SQL>
Home »
Oracle »
Select » 

Hierarchical Queries:
  1. Hierarchical Queries
  2. Formatting the Results from a Hierarchical Query
  3. Starting at a non-root Node using the START WITH clause.
  4. Using a Subquery in a START WITH Clause
  5. Traversing Upward Through the Tree
Related: