Find all childrens

• kasper-383322

  Old Hand

  Points: 349

  More actions

  December 14, 2006 at 3:17 pm

  #95022

  Hi,

  Can anybody suggest how to find all the children of the particular ancestor from the table where the family tree stored:

  CREATE TABLE family (id int NOT NULL PRIMARY KEY, id_ancestor_m int NOT NULL, first_name varchar (80), second name varchar(80))?

  This should be resolved with SELECT statement.

   

  Thanks.

• MVDBA (Mike Vessey)

  SSC-Insane

  Points: 21797

  More actions

  December 15, 2006 at 2:37 am

  #678275

  i assume you're looking for someting a little more complicated than

  select * from family where id_ancestor_m=123456

  you're looking for a complete chain of descendants rather than just the children??

  are you using sql2000 or 2005?

  2005 allows the use of CTE which would make this easier

  MVDBA

• Michael Meierruth

  SSChampion

  Points: 10051

  More actions

  December 15, 2006 at 4:14 am

  #678286

  I did the function below some time ago and it should be valid for what you need.

  It is based on a table which actually contains many hierarchical structures. Thus

  the function requires the name of the hierarchy along with the name of the node.

  The table av_element contains, among other things, the columns

  category

  element

  parent

  ALTER  function avx_nodes_below(@category varchar(16),@element varchar(30))

  /*

  Note the following:

  1. if a structure has more than six levels this produces incorrect results

  2. actually the node itself is also returned (not just the nodes below it)

  */

  returns table

  as

  return

  (

  select e6.element

  from   av_element e1,av_element e2,av_element e3, av_element e4, av_element e5, av_element e6

  where  e1.category=@category and

         e1.element=e2.parent and e2.category=@category and

         e2.element=e3.parent and e3.category=@category and

         e3.element=e4.parent and e4.category=@category and

         e4.element=e5.parent and e5.category=@category and

         e5.element=e6.parent and e6.category=@category and

         (e1.element=@element or

          e2.element=@element or

          e3.element=@element or

          e4.element=@element or

          e5.element=@element or

          e6.element=@element)

  union all

  select e5.element

  from   av_element e1,av_element e2,av_element e3, av_element e4, av_element e5

  where  e1.category=@category and

         e1.element=e2.parent and e2.category=@category and

         e2.element=e3.parent and e3.category=@category and

         e3.element=e4.parent and e4.category=@category and

         e4.element=e5.parent and e5.category=@category and

         (e1.element=@element or

          e2.element=@element or

          e3.element=@element or

          e4.element=@element or

          e5.element=@element)

  union all

  select e4.element

  from   av_element e1,av_element e2,av_element e3, av_element e4

  where  e1.category=@category and

         e1.element=e2.parent and e2.category=@category and

         e2.element=e3.parent and e3.category=@category and

         e3.element=e4.parent and e4.category=@category and

         (e1.element=@element or

          e2.element=@element or

          e3.element=@element or

          e4.element=@element)

  union all

  select e3.element

  from   av_element e1,av_element e2,av_element e3

  where  e1.category=@category and

         e1.element=e2.parent and e2.category=@category and

         e2.element=e3.parent and e3.category=@category and

         (e1.element=@element or

          e2.element=@element or

          e3.element=@element)

  union all

  select e2.element

  from   av_element e1,av_element e2

  where  e1.category=@category and

         e1.element=e2.parent and e2.category=@category and

         (e1.element=@element or

          e2.element=@element)

  union all

  select e1.element

  from   av_element e1

  where  e1.category=@category and

         e1.element=@element

  )