Prev: single user mode
Next: Debugging SQL Server 2005 Transact SQL
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 <stunaz(a)gmail.com>
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 mode
Next: Debugging SQL Server 2005 Transact SQL