Getting Children of Parent Within a table
-
I have a table Named 'Roles' which contains 'ID' and 'ParentID' fields. Some Sample Data is ID ,Name ,ParentID 1 ,Admin ,0 2 ,Manager ,1 3 ,Front Office User ,1 4 ,Supervisor ,2 5 ,Inventory User ,0 6 ,Human Resource User ,4 I would like to extract data of Parent and all children. SELECT b.[ID] as 'Parent', a.[ID] as 'Role' FROM Roles a INNER JOIN Roles b ON a.ParentID = b.[ID] and a.ParentID=1 which can return only. ID ,ParentID 1 ,2 1 ,3 My wanted form is ParentID ,ID 1 ,2 1 ,3 2 ,4 4 ,6 Like an organization tree, the top parent level can access all lower level nodes. Sql 2005 support that feature. How can I access them in Sql2000 recursive function or store procedure? Thanks May Thu San.
-
I have a table Named 'Roles' which contains 'ID' and 'ParentID' fields. Some Sample Data is ID ,Name ,ParentID 1 ,Admin ,0 2 ,Manager ,1 3 ,Front Office User ,1 4 ,Supervisor ,2 5 ,Inventory User ,0 6 ,Human Resource User ,4 I would like to extract data of Parent and all children. SELECT b.[ID] as 'Parent', a.[ID] as 'Role' FROM Roles a INNER JOIN Roles b ON a.ParentID = b.[ID] and a.ParentID=1 which can return only. ID ,ParentID 1 ,2 1 ,3 My wanted form is ParentID ,ID 1 ,2 1 ,3 2 ,4 4 ,6 Like an organization tree, the top parent level can access all lower level nodes. Sql 2005 support that feature. How can I access them in Sql2000 recursive function or store procedure? Thanks May Thu San.
-
I think you made a little mistake in your query. This should work:
SELECT b.[ID] as 'Parent', a.[ID] as 'Role' FROM Roles a INNER JOIN Roles b ON a.ParentID = b.[ID]Wout Louwers
Sorry, My Query is SELECT b.[ID] as 'Parent', a.[ID] as 'Role' FROM Roles a INNER JOIN Roles b ON a.[ParentID] = b.[ID] and a.ParentID=1 Some Sample Data is ID ,Name ,ParentID 1 ,Admin ,0 2 ,Manager ,1 3 ,Front Office User ,1 4 ,Supervisor ,2 5 ,Inventory User ,0 6 ,Human Resource User ,4 The above query can only show the first two child of ID 1 (which are 2,3) and did not show the child of parent 2 and 3...etc. How can I get all children?
-
Sorry, My Query is SELECT b.[ID] as 'Parent', a.[ID] as 'Role' FROM Roles a INNER JOIN Roles b ON a.[ParentID] = b.[ID] and a.ParentID=1 Some Sample Data is ID ,Name ,ParentID 1 ,Admin ,0 2 ,Manager ,1 3 ,Front Office User ,1 4 ,Supervisor ,2 5 ,Inventory User ,0 6 ,Human Resource User ,4 The above query can only show the first two child of ID 1 (which are 2,3) and did not show the child of parent 2 and 3...etc. How can I get all children?
May Thu san wrote:
The above query can only show the first two child of ID 1 (which are 2,3) and did not show the child of parent 2 and 3...etc. How can I get all children?
by removing the "and a.ParentID=1" from the query.
Upcoming Scottish Developers events: * Glasgow: Tell us what you want to see in 2007 My: Website | Blog | Photos
-
Sorry, My Query is SELECT b.[ID] as 'Parent', a.[ID] as 'Role' FROM Roles a INNER JOIN Roles b ON a.[ParentID] = b.[ID] and a.ParentID=1 Some Sample Data is ID ,Name ,ParentID 1 ,Admin ,0 2 ,Manager ,1 3 ,Front Office User ,1 4 ,Supervisor ,2 5 ,Inventory User ,0 6 ,Human Resource User ,4 The above query can only show the first two child of ID 1 (which are 2,3) and did not show the child of parent 2 and 3...etc. How can I get all children?