these things are much easier in kdb+
you could also use foreign keys for some convenience
(which are just replacing values with pointers to values)
q)t:Employee xcol delete ManagerID from update Manager:
t$ManagerID from t:1!t
q)t
Employee| FirstName LastName Title DeptID Manager
--------| --------------------------------------------------------------
1 | Ken Sanchez Chief Executive Officer 16
273 | Brian Welcker Vice President of Sales 3 1
274 | Stephen Jiang North American Sales Manager 3 273
275 | Michael Blythe Sales Representative 3 274
276 | Linda Mitchell Sales Representative 3 274
285 | Syed Abbas Pacific Sales Manager 3 273
286 | Lynn Tsoflias Sales Representative 3 285
16 | David Bradley Marketing Manager 4 273
23 | Mary Gibson Marketing Specialist 4 16
this makes queries rather convenient:
what is the first name and department of my Manager?s Manager?
q)select Manager.Manager.FirstName,Manager.Manager.DeptID from t
FirstName DeptID
----------------
Ken 16
Brian 3
Brian 3
Ken 16
Brian 3
Ken 16
Brian 3
who report to the CEO directly?
q)select from t where Manager.Title like “Chief Executive Officer”
Employee| FirstName LastName Title DeptID Manager
--------| ---------------------------------------------------------
273 | Brian Welcker Vice President of Sales 3 1
To get department Level you could chase the Manager up
q)update chain:(Manager scan)each Manager from t
Employee| FirstName LastName Title DeptID Manager chain
--------| -------------------------------------------------------------------------------
1 | Ken Sanchez Chief Executive Officer 16 t$,0Ni<br>273 | Brian Welcker Vice President of Sales 3 1
t$1 0Ni
274 | Stephen Jiang North American Sales Manager 3 273 t$273 1 0Ni<br>275 | Michael Blythe Sales Representative 3 274
t$274 273 1 0Ni
276 | Linda Mitchell Sales Representative 3 274 t$274 273 1 0Ni<br>285 | Syed Abbas Pacific Sales Manager 3 273
t$273 1 0Ni
286 | Lynn Tsoflias Sales Representative 3 285 t$285 273 1 0Ni<br>16 | David Bradley Marketing Manager 4 273
t$273 1 0Ni
23 | Mary Gibson Marketing Specialist 4 16 `t$16 273 1 0Ni
to get the Level you just count the chaing
q)update Level: -1+count each(Manager scan)each Manager from t
Employee| FirstName LastName Title DeptID Manager Level
--------| --------------------------------------------------------------------
1 | Ken Sanchez Chief Executive Officer 16 0
273 | Brian Welcker Vice President of Sales 3 1 1
274 | Stephen Jiang North American Sales Manager 3 273 2
275 | Michael Blythe Sales Representative 3 274 3
276 | Linda Mitchell Sales Representative 3 274 3
285 | Syed Abbas Pacific Sales Manager 3 273 2
286 | Lynn Tsoflias Sales Representative 3 285 3
16 | David Bradley Marketing Manager 4 273 2
23 | Mary Gibson Marketing Specialist 4 16 3
Or you could do the scan for everyone at the same time (which is usually faster)
This will keep iterating until there is a layer in the hierarchy
(i.e. every chain has four items, padded with null)
q)update chain:flip Manager scan Manager from t
Employee| FirstName LastName Title DeptID Manager chain
--------| -------------------------------------------------------------------------
1 | Ken Sanchez Chief Executive Officer 16
273 | Brian Welcker Vice President of Sales 3 1 1
274 | Stephen Jiang North American Sales Manager 3 273 273 1
275 | Michael Blythe Sales Representative 3 274 274 273 1
276 | Linda Mitchell Sales Representative 3 274 274 273 1
285 | Syed Abbas Pacific Sales Manager 3 273 273 1
286 | Lynn Tsoflias Sales Representative 3 285 285 273 1
16 | David Bradley Marketing Manager 4 273 273 1
23 | Mary Gibson Marketing Specialist 4 16 16 273 1
so consequently you get the level a bit differently
q)update Level:sum not null Manager scan Manager from t
Employee| FirstName LastName Title DeptID Manager Level
--------| --------------------------------------------------------------------
1 | Ken Sanchez Chief Executive Officer 16 0
273 | Brian Welcker Vice President of Sales 3 1 1
274 | Stephen Jiang North American Sales Manager 3 273 2
275 | Michael Blythe Sales Representative 3 274 3
276 | Linda Mitchell Sales Representative 3 274 3
285 | Syed Abbas Pacific Sales Manager 3 273 2
286 | Lynn Tsoflias Sales Representative 3 285 3
16 | David Bradley Marketing Manager 4 273 2
23 | Mary Gibson Marketing Specialist 4 16 3
You should read Stevan Apter’s excellent article on what else is possible with pointer chasing:
http://archive.vector.org.uk/art10500340
Cheers,
Attila