hi
who to create functions one return child nodes as per Case 1 and another one is return Parent Nodes as per case 2 and case 3.
Case 1: (Retrieval of the child nodes)
On passing the User ID of (Tom) , should get the Output as User id of ( Ross, Joe, Bryan , John, Adam and Jobin) along with their Role ID.
On passing the User ID of (Jim) , should get the Output as User ID of (Ross, Joe and Bryan) along with their Role ID.
Case 2: (Retrieval of Parent Nodes)
On passing the Role ID of (R3), should get the User ID of all the Parent roles( Jim and Sadra (R2) , Tom (R1)) along with their Role id
Case 3: on passing role id of R3 should get only specifically parents of particular child node.
i have 3 master tables 1)RoleDetails(Roleid(PK),name,masterroleid(fk) ref:RoleDetails roleid)
2) PositionDetails(positionid(PK), name,MasterPositionid(FK) ref:PostionDetails postionid,Roleid(fk) ref:Roledetails roleid)
3) Userdetails(userid(pk), loginid,pwd,roleid(fk) ref:roledetails roleid,positionid(fk)refostionDetails positionid,fname,address)
how to Create two functions one return child nodes as per Case 1 and another one is return Parent Nodes
as per case 2
(Manager) a -- r1 (roledetails)
/ \

(ROL)a1 (ROL) a2 -- r2
/ | \ / | | \
(RO)b1 b2 b3 b4 b5 b6 -- r3

note that the structure may change if i want to increase more rows and postionids. plz get me out of this. thanks in adv.,

chakri