/************************************************
                 _______Nancy_______
                /                   |
          Andrew                     Janet
         /      \                   /     |  
  Margaret      Steven        Michael      Robert
    /  \         /  \          /  |
Laura  Ann    Ina   David   Ron   Dan
*************************************************/
Declare @nodes table (NodeId int, Name varchar(20), side int)
Insert Into @nodes Values(1,'nancy',0)
Insert Into @nodes Values(2,'andrew',0)
Insert Into @nodes Values(3,'janet',1)
Insert Into @nodes Values(4,'margaret',0)
Insert Into @nodes Values(5,'steven',1)
Insert Into @nodes Values(6,'michael',0)
Insert Into @nodes Values(7,'robert',1)
Insert Into @nodes Values(8,'laura',0)
Insert Into @nodes Values(9,'ann',1)
Insert Into @nodes Values(10,'ina',0)
Insert Into @nodes Values(11,'david',1)
Insert Into @nodes Values(12,'ron',0)
Insert Into @nodes Values(13,'dan',1)

Declare @tree table (pNodeId int, cNodeId int, processed bit default(0))
Insert Into @tree (pNodeId, cNodeId) Values (0,1)
Insert Into @tree (pNodeId, cNodeId) Values (1,2)
Insert Into @tree (pNodeId, cNodeId) Values (1,3)
Insert Into @tree (pNodeId, cNodeId) Values (2,4)
Insert Into @tree (pNodeId, cNodeId) Values (2,5)
Insert Into @tree (pNodeId, cNodeId) Values (3,6)
Insert Into @tree (pNodeId, cNodeId) Values (3,7)
Insert Into @tree (pNodeId, cNodeId) Values (4,8)
Insert Into @tree (pNodeId, cNodeId) Values (4,9)
Insert Into @tree (pNodeId, cNodeId) Values (5,10)
Insert Into @tree (pNodeId, cNodeId) Values (5,11)
Insert Into @tree (pNodeId, cNodeId) Values (6,12)
Insert Into @tree (pNodeId, cNodeId) Values (6,13)

--Select * From @Tree


Declare @pad nvarchar(100),
	@lastCnt int
Set @Pad = '0000'

Declare @paths table (path nvarchar(1000), pNodeId int, cNodeId int)

Insert Into @paths
Select 
	path=right(@pad + convert(nvarchar,pNodeId),len(@pad))+';' + right(@pad + convert(nvarchar,cNodeId),len(@pad))+';',
	pNodeId,
	cNodeId
From @Tree where pNodeId=0

Update A
Set Processed = 1
From @Tree as A
Inner Join @paths as B
On A.pNodeId = B.pNodeId
and A.cNodeId = B.cNodeId

While exists(Select * From @tree Where Processed = 0)
Begin
	Insert Into @paths
	Select 
		path=path + case when B.cNodeId is not null then right(@pad + convert(nvarchar,B.cNodeId),len(@pad))+';' else '' end,
		B.pNodeId,
		B.cNodeId
	From @Paths as A
	Left Join @Tree as B
	On A.cNodeId = B.pNodeId
	Where B.Processed = 0

	Update A
	Set Processed = 1
	From @Tree as A
	Inner Join @paths as B
	On A.pNodeId = B.pNodeId
	and A.cNodeId = B.cNodeId
	Where A.Processed = 0
End

--All paths to each nodeId
--Select path, cNodeId From @paths Where cNodeId is not null




Declare @nodeName varchar(20),
	@side varchar(10),
	@nodePath varchar(1000)

Set @nodeName = 'andrew'
Set @side = 'right'

Set @nodePath = (Select path From @paths Where cNodeId = (Select nodeId From @nodes where Name = @nodeName))

	Select top 1 name
	From
		(
		Select
			path, 
			cNodeId, 
			side = case when @side='left' then abs(side-1) else side end
		From @paths A
		Inner Join @nodes B
		On right(A.path,len(A.path)-len(@nodePath)) like ('%'+right(@pad+convert(nvarchar,B.nodeId),len(@pad))+';%')
		Where path like (@nodePath + '%')
		) Z
	Inner Join @nodes Y
	On Z.cNodeId = Y.nodeId
	Group By path, cNodeId, name
	Having count(*)=sum(Z.side)
	Order by sum(Z.side) desc