|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-02-16 : 08:01:09
|
| pp writes "hi my tables areCreate Table PositionMaster ((PK) positionid bigint, [Name] varchar(20), [Desc] varchar(50), (FK:Ref: PositionMaster)Parent PositionID bigint, (FK:Ref: RoleMaster)RoleId bigint, Status bit )Create Table RoleMaster ((PK) RoleId bigint,[Name] varchar(20), [Desc} varchar (50), (FK:Ref: RoleMaster)ParentRoleId bigint, Status bit )Create Table UserMaster ( (PK)UserId bigint,LoginId varchar(50),Pasword varchar(50), (FK:Ref: RoleMaster) RoleId bigint, (FK:Ref: UserMaster) PositionId bigint, Status bit, FirstName varchar(50), LastName varchar(50), Gender varchar(6) ,ContactNum bitint, Address Varchar(100), Email varchar(50) )and i inserted data as 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) 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 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,'Null','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,'Null','Null') Insert into UserMaster values (9,'Jobin','Jobin',4,9,1,'Jobin','Thomas','m',Null,'Null','Null') question1: in fuction On passing the User ID of (tom) , should get the Output as User id of last leaves/nodes. Question 2:in fuction On passing the Role ID of (R3), should get the User ID of all the Parent roles long with their Role id .. note that the positionids and rowids may increase. i mean a function for any situation if child nodes may increase .. get me out of this plz. thanks in adv.,--pp" |
|
functions on parent and child nodes 