function on heirarchy nodes

Hi
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)ref:postionDetails 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
Case 1:
On passing the User ID of (a) , should get the Output as User id of ( b1,b2,b3,b4,b5,b6) along with their Role ID.

On passing the User ID of (a1), should get the Output as User ID of (b1,b2,b3)along with their Role ID.

Case 2:
On passing the Role ID of (R3), should get the User ID of all the Parent roles( a1 and a2 (R2), a(R1)long with their Role id
Case 3: on passing role id of child node , should get only all particular parent userid and roleid's.

thanks in adv.,
chakriPost the DDL of the tables.it is lot easier to help u.|||sorry i was unable to understand as i am also new to sql. dont mind, can u gve me script for 3 seperate functions as per my requierment. or just gve me seperate queries which can produce to my requirement.
chakri|||sorry i was unable to understand as i am also new to sql. dont mind, can u gve me script for 3 seperate functions as per my requierment. or just gve me seperate queries which can produce to my requirement.
chakri

read this post first.http://www.dbforums.com/showthread.php?t=1196943|||CREATE TABLE [dbo].[PositionMaster] (
[PositionID] [bigint] NOT NULL ,
[Name] [varchar] (20) COLLATE Latin1_General_CS_AS NULL ,
[Desc] [varchar] (200) COLLATE Latin1_General_CS_AS NULL ,
[ParentPositionID] [bigint] NULL ,
[RoleID] [bigint] NULL ,
[Status] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RoleMaster] (
[RoleID] [bigint] NOT NULL ,
[Name] [varchar] (20) COLLATE Latin1_General_CS_AS NULL ,
[Desc] [varchar] (200) COLLATE Latin1_General_CS_AS NULL ,
[ParentRoleID] [bigint] NOT NULL ,
[Status] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserMaster] (
[UserID] [bigint] NOT NULL ,
[LoginID] [varchar] (100) COLLATE Latin1_General_CS_AS NOT NULL ,
[Password] [varchar] (50) COLLATE Latin1_General_CS_AS NOT NULL ,
[RoleID] [bigint] NOT NULL ,
[PositionID] [bigint] NOT NULL ,
[Status] [bit] NULL ,
[FirstName] [varchar] (50) COLLATE Latin1_General_CS_AS NULL ,
[LastName] [varchar] (50) COLLATE Latin1_General_CS_AS NULL ,
[Gender] [varchar] (6) COLLATE Latin1_General_CS_AS NULL ,
[ContactNum] [bigint] NULL ,
[Address] [varchar] (200) COLLATE Latin1_General_CS_AS NULL ,
[Email] [varchar] (50) COLLATE Latin1_General_CS_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PositionMaster] ADD
CONSTRAINT [PK_PositionMaster] PRIMARY KEY CLUSTERED
(
[PositionID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[RoleMaster] ADD
CONSTRAINT [PK_RoleMaster] PRIMARY KEY CLUSTERED
(
[RoleID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[UserMaster] ADD
CONSTRAINT [PK_UserMaster] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PositionMaster] ADD
CONSTRAINT [FK_PositionMaster_PositionMaster] FOREIGN KEY
(
[ParentPositionID]
) REFERENCES [dbo].[PositionMaster] (
[PositionID]
),
CONSTRAINT [FK_PositionMaster_RoleMaster] FOREIGN KEY
(
[RoleID]
) REFERENCES [dbo].[RoleMaster] (
[RoleID]
)
GO

ALTER TABLE [dbo].[RoleMaster] ADD
CONSTRAINT [FK_RoleMaster_RoleMaster] FOREIGN KEY
(
[ParentRoleID]
) REFERENCES [dbo].[RoleMaster] (
[RoleID]
)
GO

ALTER TABLE [dbo].[UserMaster] ADD
CONSTRAINT [FK_UserMaster_PositionMaster] FOREIGN KEY
(
[PositionID]
) REFERENCES [dbo].[PositionMaster] (
[PositionID]
),
CONSTRAINT [FK_UserMaster_RoleMaster] FOREIGN KEY
(
[RoleID]
) REFERENCES [dbo].[RoleMaster] (
[RoleID]
)
GO|||if u ask for DML here is it..

Insert into RoleMaster values (1,'Admin','Adminstrator',1,1)
Insert into RoleMaster values (2,'Mgr','Manager',1,2)
Insert into RoleMaster values (3,'RoL','Recovery Operator Lead',2,3)
Insert into RoleMaster values (4,'RO','Recovery Operator',3,4)
select * from RoleMaster
go

Insert into PositionMaster values (1,'Mgr','Manager',Null,1,1)
Insert into PositionMaster values (2,'ROL1','Recovery Operator Lead 1',1,2,1)
Insert into PositionMaster values (3,'ROL2','Recovery Operator Lead 2',1,2,1)
Insert into PositionMaster values (4,'RO1','Recovery Operator 1',2,3,1)
Insert into PositionMaster values (5,'RO2','Recovery Operator 2',2,3,1)
Insert into PositionMaster values (6,'RO3','Recovery Operator 3',3,3,1)
Insert into PositionMaster values (7,'RO4','Recovery Operator 4',3,3,1)
Insert into PositionMaster values (8,'RO5','Recovery Operator 5',3,3,1)
Insert into PositionMaster values (9,'RO6','Recovery Operator 6',3,3,1)

go
select * from PositionMaster

Insert into UserMaster values (1,'Tom','Tom',2,1,1,'Tom','Hanks','m',Null,'Null' ,'Null')
Insert into UserMaster values (2,'Jim','Jim',3,2,1,'Jim','Ward','m',Null,'Null', 'Null')
Insert into UserMaster values (3,'Sandra','Sandra',3,3,1,'Sandra','Bullock','m', Null,'Null','Null')
Insert into UserMaster values (4,'Ross','Ross',4,4,1,'Ross','Magan','m',Null,'Nu ll','Null')
Insert into UserMaster values (5,'Joe','Joe',4,5,1,'Joe','Vester','m',Null,'Null ','Null')
Insert into UserMaster values (6,'Bryan','Bryan',4,6,1,'Byran','Adam','m',Null,' Null','Null')
Insert into UserMaster values (7,'John','John',4,7,1,'Jhon','Abraham','m',Null,' Null','Null')
Insert into UserMaster values (8,'Adam','Adam',4,8,1,'Adam','Core','m',Null,'Nul l','Null')
Insert into UserMaster values (9,'Jobin','Jobin',4,9,1,'Jobin','Thomas','m',Null ,'Null','Null')
select * from UserMaster|||Now give me a sample output of ur result u r looking|||Case 1: (Retrieval of the child nodes)

Example !: 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.

Example 2: On passing the User ID of (Jim) , should get the Output as User ID of (Ross, Joe ) 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 the Role Id of child node, should get the User ID's of all specific parent roles along with Role id.
Note that i may increase the rows and position id's|||case 1,2

--Case Tom--
declare @.UserID int
set @.UserID=1
select

u2.UserID ,
u2.LoginID,
u2.RoleID
from UserMaster u1 join RoleMaster r1
on u1.RoleID=r1.ParentRoleID
join
UserMaster u2
on r1.RoleID=u2.RoleID
where u1.UserID=@.UserID
go
--case Jim---
declare @.UserID int
set @.UserID=2
select

u2.UserID ,
u2.LoginID,
u2.RoleID
from UserMaster u1 join RoleMaster r1
on u1.RoleID=r1.ParentRoleID
join
UserMaster u2
on r1.RoleID=u2.RoleID
where u1.UserID=@.UserID|||i gave as example 1 and example 2 for case 1 as tom and jim. anyway by passing @.userid =1 its not displaying all the nodes.. its just retriving
Userid Loginid Roleid
2 Jim 3
3 Sandra 3
but as per my requirement On passing the User ID of (Tom) , should get the Output of Ross, Joe, Bryan , John, Adam and Jobin.

and for jim which ur query is displaying
userid loginid roleid
4 Ross 4
5 Joe 4
6 Bryan 4
7 John 4
8 Adam 4
9 Jobin 4
but it need to disply only for Ross and Joe details..|||i gave as example 1 and example 2 for case 1 as tom and jim. anyway by passing @.userid =1 its not displaying all the nodes.. its just retriving
Userid Loginid Roleid
2 Jim 3
3 Sandra 3
but as per my requirement On passing the User ID of (Tom) , should get the Output of Ross, Joe, Bryan , John, Adam and Jobin.

I will explain what i undestand,

1.based on userid I got the roleID
2.select RoleID from RoleMaster by equating roleID from 1 step with ParentRoleID.
3.select userID from PostionMaster based whoever having that roleID I got from 2 step.
---

based on userid Tom's role id is 2
roleid 3 has parentRoleID 2(tom's roleid)
jim and sandra have roleid 3 in usermaster|||i will explain my scenario.
when i gve userid of Tom, condition is all the child nodes should be retrived, unconditionally how many may be the child nodes. all child nodes should be retrived. this is in case 1.
case 2. is when i gve roleid of anychild node, all the parent nodes should be retrived. it means if child node is not in the hand of parent node also all the parents should be output.
case 3: if i gve roleid of anychild node only the corresponding all the parents should be retived. it means it is hand based. if child is said to particular parent and that parent is in hand of another parent.. all related parents should be retrived.
i hope u can understand by this. i was unable to get this solution from last 2 days.|||mallier i am waiting for ur reply.. can i expect ur further help-hand.|||mallier i am waiting for ur reply.. can i expect ur further help-hand.

I forgot ur post itself.let me try now .|||Here is an article I wrote on a method of returning data from hierarchical structures. You should be able to adapt this to your situation.
-------------------
The most flexible and robust method of storing hierarchical data in a database is to use a table with a recursive relationship. In this design, each record has an associated parent record ID that indicates its relative place in the hierarchy. Here is an example:

CREATE TABLE [YourTable]
([RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[ParentID] [int] NULL)

The challenge is to find a way to return all the child records and descendants for any given parent record.

While recursion is supported within SQL Server, it is limited to 32 nested levels and it tends to be ineffecient because it does not take full advantage of SQL Server's set-based operations.

A better algorithm is a method I call the "Accumulator Table".

In this method, a temporary table is declared that accumulates the result set. The table is seeded with the initial key of the parent record, and then a loop is entered which inserts the immediate descendants of all the records accumulated so far which have not already been added to the table.

Here is some skeleton code to show how it works:

--This variable will hold the parent record ID who's children we want to find.
declare @.RecordID int
set @.RecordID = 13

--This table will accumulate our output set.
declare @.RecordList table (RecordID int)

--Seed the table with the @.RecordID value, assuming it exists in the database.
insert into @.RecordList (RecordID)
select RecordID
from YourTable
where YourTable.RecordID = @.RecordID

--Add new child records until exhausted.
while @.@.RowCount > 0
insert into @.RecordList (RecordID)
select YourTable.RecordID
from YourTable
inner join @.RecordList RecordList on YourTable.ParentID = RecordList.RecordID
where not exists (select * from @.RecordList CurrentRecords where CurrentRecords.RecordID = YourTable.RecordID)

--Return the result set
select RecordID
from @.RecordList

This method is both flexible and efficient, and the concept is adaptable to other hierarchical data challenges.

For a completely different method of storing and manipulating hierarchical data, check out Celko's Nested Set model, which stores relationships as loops of records.

http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=145525%5D|||ya i had a look at that site also, but can u explain how in Personnel table , fo albert and bert... got 12 and 3.. as rgt. as lft=2*(SELECT COUNT(*) FROM TreeTable) ..and sorry for asking u directly .. could u give me direct procedure for my requirement, as i am working for these, last week days . i am trying in different ways and moved to another. plz do if possible. thanks for what u r doing.|||--case 1

create procedure dbo.retrieveUserid (
@.roleid int
)
as
if object_id('tempdb.dbo.##temp','u') is null
begin
create table ##temp(userid int,roleid int)
print('create table')
end
declare @.roleid1 int
select
@.roleid1=r1.RoleID
from UserMaster u1 join RoleMaster r1
on u1.RoleID=r1.ParentRoleID
join
UserMaster u2
on r1.RoleID=u2.RoleID
where u1.RoleID=@.roleid
if (@.@.rowcount>0)
begin
insert into ##temp
select distinct
u2.UserID ,
r1.RoleID
from UserMaster u1 join RoleMaster r1
on u1.RoleID=r1.ParentRoleID
join
UserMaster u2
on r1.RoleID=u2.RoleID
where u1. RoleID= @.roleid

exec retrieveUserid @.roleid1
end
else
begin
select * from ##temp
drop table ##temp
end

--Call procedure--
declare @.roleid int,@.userid int
set @.userid=1
select @.roleid=RoleID from UserMaster where UserID= @.userid
exec retrieveUserid @.roleid