Hello SQL gurus,
There are recursive queries available to find all children or parents in a hierarchy (for example in an employee-manager hierarchy) but I couldn't find any example of peers and above/parents
EmpID FName LName MgrID
11 Sally Smith NULL
1 Alex Adams 11
2 Barry Brown 11
3 Lee Osaka 11
4 David Kennson 11
5 Eric Bender 11
7 David Lonning 11
6 Lisa Kendall 4
8 John Marshbank 4
12 Barbara ONeill 4
13 Phil Wilconkinski 4
9 James Newton 3
10 Terry OHaire 3
So if I want peer-above of EmpID=6 then it should return
EmpID FName LName MgrID
11 Sally Smith NULL grandparent/peer
1 Alex Adams 11 parent-peer
2 Barry Brown 11 parent-peer
3 Lee Osaka 11 parent-peer
4 David Kennson 11 (parent of id=6)
5 Eric Bender 11 parent-peer
7 David Lonning 11 parent-peer
6 Lisa Kendall 4 (id=6)
8 John Marshbank 4 peer
12 Barbara ONeill 4 peer
13 Phil Wilconkinski 4 peer
Any help will be very appreciated