Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2006
    Posts
    42

    Unanswered: 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)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
    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.,
    chakri

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Post the DDL of the tables.it is lot easier to help u.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Feb 2006
    Posts
    42
    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

  4. #4
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Quote Originally Posted by 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
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Feb 2006
    Posts
    42
    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

  6. #6
    Join Date
    Feb 2006
    Posts
    42
    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

  7. #7
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up

    Now give me a sample output of ur result u r looking
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  8. #8
    Join Date
    Feb 2006
    Posts
    42
    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
    Last edited by chakri; 02-03-06 at 10:30.

  9. #9
    Join Date
    Jun 2003
    Posts
    269
    case 1,2
    Code:
    ---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 love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  10. #10
    Join Date
    Feb 2006
    Posts
    42
    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..
    Last edited by chakri; 02-03-06 at 10:36.

  11. #11
    Join Date
    Jun 2003
    Posts
    269

    Exclamation

    Quote Originally Posted by chakri
    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
    Last edited by mallier; 02-03-06 at 10:40.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  12. #12
    Join Date
    Feb 2006
    Posts
    42
    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.

  13. #13
    Join Date
    Feb 2006
    Posts
    42
    mallier i am waiting for ur reply.. can i expect ur further help-hand.

  14. #14
    Join Date
    Jun 2003
    Posts
    269
    Quote Originally Posted by chakri
    mallier i am waiting for ur reply.. can i expect ur further help-hand.
    I forgot ur post itself.let me try now .
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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...stid=145525%5D
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •