How do I get both the child and one upper level parent information by using oracle connect by prior?

Question

I want to get both the child and one upper level parent information by using oracle connect by prior?

For example the folowing query retrieve child info and parent id,

SELECT last_name, employee_id, manager_id, LEVEL
  FROM employees
  START WITH employee_id = 100
  CONNECT BY PRIOR employee_id = manager_id

but I want to get parent info also like

  LAST_NAME                 EMPLOYEE_ID MANAGER_ID      LEVEL   MANAGER_NAME
 ------------------------- ----------- ---------- ----------------------------
 King                              100                     1         ?
 Cambrault                         148        100          2         ?
 Bates                             172        148          3         ?
 Bloom                             169        148          3         .
 Fox                               170        148          3         .

How can I handle this problem, when I applied left join after selecting childs by connect by prior,The objects order is mixing.

Answer 1

You can refer to prior values in the select list:

SELECT last_name, employee_id, manager_id, LEVEL, prior last_name as manager_name
  FROM employees
  START WITH employee_id = 100
  CONNECT BY PRIOR employee_id = manager_id;

LAST_NAME                 EMPLOYEE_ID MANAGER_ID      LEVEL MANAGER_NAME            
------------------------- ----------- ---------- ---------- -------------------------
King                              100                     1                          
Kochhar                           101        100          2 King                     
Greenberg                         108        101          3 Kochhar                  
Faviet                            109        108          4 Greenberg                
...
Cambrault                         148        100          2 King                     
Bates                             172        148          3 Cambrault                
Bloom                             169        148          3 Cambrault                
Fox                               170        148          3 Cambrault                
...