From: stunaz on 15 Jun 2010 19:01 Hello, i am trouble trying to do a SELECT . i have one table : persons (person_id, person_child,distance) WHERE distance is the number of generation between the person_id andpersonn_child_id. here is ans example of data : (1,1,0) --> himselsf (2,1,1) --> the child if person 1 (3,2,1) --> the child of person 2 (4,2,1) --> the child of person 2 (4,1,2) --> the child of person 1 ==> level 2 (4,4,0) --> himselsf (5,2,1) --> the child of person 2 (5,1,2) --> the child of person 1 ==> level 2 (6,5,1) --> the child of person 5 (7,1,1) --> the child of person 1 .... i want to list this table to get childs firsts by parents starting with the last level (7,1,1) (4,2,1) (3,2,1) (2,1,1) (6,5,1) (5,2,1) (1,1,0) its like child grouped by level and by tree. any idea or any reference? From: John Bell on 16 Jun 2010 04:03 On Tue, 15 Jun 2010 16:01:13 -0700 (PDT), stunaz wrote: >Hello, >i am trouble trying to do a SELECT . >i have one table : persons (person_id, person_child,distance) > >WHERE distance is the number of generation between the person_id >andpersonn_child_id. here is ans example of data : > >(1,1,0) --> himselsf >(2,1,1) --> the child if person 1 >(3,2,1) --> the child of person 2 >(4,2,1) --> the child of person 2 >(4,1,2) --> the child of person 1 ==> level 2 >(4,4,0) --> himselsf >(5,2,1) --> the child of person 2 >(5,1,2) --> the child of person 1 ==> level 2 >(6,5,1) --> the child of person 5 >(7,1,1) --> the child of person 1 >... > >i want to list this table to get childs firsts by parents starting >with the last level > >(7,1,1) > >(4,2,1) >(3,2,1) >(2,1,1) > >(6,5,1) >(5,2,1) > >(1,1,0) > >its like child grouped by level and by tree. > >any idea or any reference? Hi It seems that some of your data is not needed and you can reduce your hierarchy to something like: (1,null,0) --> self (2,1,1) --> the child if person 1 (3,2,1) --> the child of person 2 (4,2,1) --> the child of person 2 (5,2,1) --> the child of person 2 (6,5,1) --> the child of person 5 (7,1,1) --> the child of person 1 This is an adjacency model, and you can use a CTE to traverse it, there are examples of this in books online. If you have SQL 2008 then you can use a hierarchyid and BOL has the code to create hierachyids from and adjacency model. HierarchId have some methods that could make this easier. John From: stunaz on 16 Jun 2010 08:37 thanks a lot for your answer i'll try to look at adjacency model  |  Pages: 1 Prev: single user modeNext: Debugging SQL Server 2005 Transact SQL