-- Query the Oracle HR Employees table to show the company hierarchy. Works with Oracle 10g
SELECT employee_id, manager_id,
LPAD(' ', (LEVEL - 1) * 3) || last_name || ' ' || first_name AS Employee, -- Padding to better show the hierarchy
LEVEL, -- Pseudo Oracle column indicate the nested level
CONNECT_BY_ROOT last_name His_Boss, -- Indicate who is the GranFather
DECODE(CONNECT_BY_ISLEAF, 1, 'No', 'Yes') has_kids, -- Indicate if there are children for the node
SYS_CONNECT_BY_PATH (last_name, ' ->') hierarchy_path -- Indicate the node path
FROM employees
CONNECT BY PRIOR employee_id = manager_id -- Indicate the field in relationship
START WITH manager_id IS NULL -- Indicate from wich level to start
ORDER SIBLINGS BY manager_id -- Mantain the natural order
====================================================================================================================================
EMPLOYEE_ID MANAGER_ID EMPLOYEE LEVEL HIS_BOSS HAS_KIDS HIERARCHY_PATH
-------------------------------------------------------------------------------------------------------------------------------------
100 (null) King Steven 1 King Yes ->King
101 100 Kochhar Neena 2 King Yes ->King ->Kochhar
108 101 Greenberg Nancy 3 King Yes ->King ->Kochhar ->Greenberg
109 108 Faviet Daniel 4 King No ->King ->Kochhar ->Greenberg ->Faviet
110 108 Chen John 4 King No ->King ->Kochhar ->Greenberg ->Chen
111 108 Sciarra Ismael 4 King No ->King ->Kochhar ->Greenberg ->Sciarra
112 108 Urman Jose Manuel 4 King No ->King ->Kochhar ->Greenberg ->Urman
113 108 Popp Luis 4 King No ->King ->Kochhar ->Greenberg ->Popp
200 101 Whalen Jennifer 3 King No ->King ->Kochhar ->Whalen
203 101 Mavris Susan 3 King No ->King ->Kochhar ->Mavris
204 101 Baer Hermann 3 King No ->King ->Kochhar ->Baer
205 101 Higgins Shelley 3 King Yes ->King ->Kochhar ->Higgins
206 205 Gietz William 4 King No ->King ->Kochhar ->Higgins ->Gietz
102 100 De Haan Lex 2 King Yes ->King ->De Haan
103 102 Hunold Alexander 3 King Yes ->King ->De Haan ->Hunold
104 103 Ernst Bruce 4 King No ->King ->De Haan ->Hunold ->Ernst
105 103 Austin David 4 King No ->King ->De Haan ->Hunold ->Austin
106 103 Pataballa Valli 4 King No ->King ->De Haan ->Hunold ->Pataballa
107 103 Lorentz Diana 4 King No ->King ->De Haan ->Hunold ->Lorentz
114 100 Raphaely Den 2 King Yes ->King ->Raphaely
115 114 Khoo Alexander 3 King No ->King ->Raphaely ->Khoo
116 114 Baida Shelli 3 King No ->King ->Raphaely ->Baida
117 114 Tobias Sigal 3 King No ->King ->Raphaely ->Tobias
118 114 Himuro Guy 3 King No ->King ->Raphaely ->Himuro
119 114 Colmenares Karen 3 King No ->King ->Raphaely ->Colmenares
120 100 Weiss Matthew 2 King Yes ->King ->Weiss
125 120 Nayer Julia 3 King No ->King ->Weiss ->Nayer
126 120 Mikkilineni Irene 3 King No ->King ->Weiss ->Mikkilineni
127 120 Landry James 3 King No ->King ->Weiss ->Landry
128 120 Markle Steven 3 King No ->King ->Weiss ->Markle
180 120 Taylor Winston 3 King No ->King ->Weiss ->Taylor
181 120 Fleaur Jean 3 King No ->King ->Weiss ->Fleaur
182 120 Sullivan Martha 3 King No ->King ->Weiss ->Sullivan
183 120 Geoni Girard 3 King No ->King ->Weiss ->Geoni
121 100 Fripp Adam 2 King Yes ->King ->Fripp
129 121 Bissot Laura 3 King No ->King ->Fripp ->Bissot
130 121 Atkinson Mozhe 3 King No ->King ->Fripp ->Atkinson
131 121 Marlow James 3 King No ->King ->Fripp ->Marlow
132 121 Olson TJ 3 King No ->King ->Fripp ->Olson
184 121 Sarchand Nandita 3 King No ->King ->Fripp ->Sarchand
185 121 Bull Alexis 3 King No ->King ->Fripp ->Bull
186 121 Dellinger Julia 3 King No ->King ->Fripp ->Dellinger
187 121 Cabrio Anthony 3 King No ->King ->Fripp ->Cabrio
122 100 Kaufling Payam 2 King Yes ->King ->Kaufling
133 122 Mallin Jason 3 King No ->King ->Kaufling ->Mallin
134 122 Rogers Michael 3 King No ->King ->Kaufling ->Rogers
135 122 Gee Ki 3 King No ->King ->Kaufling ->Gee
136 122 Philtanker Hazel 3 King No ->King ->Kaufling ->Philtanker
188 122 Chung Kelly 3 King No ->King ->Kaufling ->Chung
189 122 Dilly Jennifer 3 King No ->King ->Kaufling ->Dilly
190 122 Gates Timothy 3 King No ->King ->Kaufling ->Gates
191 122 Perkins Randall 3 King No ->King ->Kaufling ->Perkins
123 100 Vollman Shanta 2 King Yes ->King ->Vollman
137 123 Ladwig Renske 3 King No ->King ->Vollman ->Ladwig
138 123 Stiles Stephen 3 King No ->King ->Vollman ->Stiles
139 123 Seo John 3 King No ->King ->Vollman ->Seo
140 123 Patel Joshua 3 King No ->King ->Vollman ->Patel
192 123 Bell Sarah 3 King No ->King ->Vollman ->Bell
193 123 Everett Britney 3 King No ->King ->Vollman ->Everett
194 123 McCain Samuel 3 King No ->King ->Vollman ->McCain
195 123 Jones Vance 3 King No ->King ->Vollman ->Jones
124 100 Mourgos Kevin 2 King Yes ->King ->Mourgos
141 124 Rajs Trenna 3 King No ->King ->Mourgos ->Rajs
142 124 Davies Curtis 3 King No ->King ->Mourgos ->Davies
143 124 Matos Randall 3 King No ->King ->Mourgos ->Matos
144 124 Vargas Peter 3 King No ->King ->Mourgos ->Vargas
196 124 Walsh Alana 3 King No ->King ->Mourgos ->Walsh
197 124 Feeney Kevin 3 King No ->King ->Mourgos ->Feeney
198 124 OConnell Donald 3 King No ->King ->Mourgos ->OConnell
199 124 Grant Douglas 3 King No ->King ->Mourgos ->Grant
145 100 Russell John 2 King Yes ->King ->Russell
150 145 Tucker Peter 3 King No ->King ->Russell ->Tucker
151 145 Bernstein David 3 King No ->King ->Russell ->Bernstein
152 145 Hall Peter 3 King No ->King ->Russell ->Hall
153 145 Olsen Christopher 3 King No ->King ->Russell ->Olsen
154 145 Cambrault Nanette 3 King No ->King ->Russell ->Cambrault
155 145 Tuvault Oliver 3 King No ->King ->Russell ->Tuvault
146 100 Partners Karen 2 King Yes ->King ->Partners
156 146 King Janette 3 King No ->King ->Partners ->King
157 146 Sully Patrick 3 King No ->King ->Partners ->Sully
158 146 McEwen Allan 3 King No ->King ->Partners ->McEwen
159 146 Smith Lindsey 3 King No ->King ->Partners ->Smith
160 146 Doran Louise 3 King No ->King ->Partners ->Doran
161 146 Sewall Sarath 3 King No ->King ->Partners ->Sewall
147 100 Errazuriz Alberto 2 King Yes ->King ->Errazuriz
162 147 Vishney Clara 3 King No ->King ->Errazuriz ->Vishney
163 147 Greene Danielle 3 King No ->King ->Errazuriz ->Greene
164 147 Marvins Mattea 3 King No ->King ->Errazuriz ->Marvins
165 147 Lee David 3 King No ->King ->Errazuriz ->Lee
166 147 Ande Sundar 3 King No ->King ->Errazuriz ->Ande
167 147 Banda Amit 3 King No ->King ->Errazuriz ->Banda
148 100 Cambrault Gerald 2 King Yes ->King ->Cambrault
168 148 Ozer Lisa 3 King No ->King ->Cambrault ->Ozer
169 148 Bloom Harrison 3 King No ->King ->Cambrault ->Bloom
170 148 Fox Tayler 3 King No ->King ->Cambrault ->Fox
171 148 Smith William 3 King No ->King ->Cambrault ->Smith
172 148 Bates Elizabeth 3 King No ->King ->Cambrault ->Bates
173 148 Kumar Sundita 3 King No ->King ->Cambrault ->Kumar
149 100 Zlotkey Eleni 2 King Yes ->King ->Zlotkey
174 149 Abel Ellen 3 King No ->King ->Zlotkey ->Abel
175 149 Hutton Alyssa 3 King No ->King ->Zlotkey ->Hutton
176 149 Taylor Jonathon 3 King No ->King ->Zlotkey ->Taylor
177 149 Livingston Jack 3 King No ->King ->Zlotkey ->Livingston
178 149 Grant Kimberely 3 King No ->King ->Zlotkey ->Grant
179 149 Johnson Charles 3 King No ->King ->Zlotkey ->Johnson
201 100 Hartstein Michael 2 King Yes ->King ->Hartstein
202 201 Fay Pat 3 King No ->King ->Hartstein ->Fay
107 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 15/ms]