SQLTeam.com | Weblogs | Forums

SQL Query Peers and above


#1

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