467,189 Members | 1,304 Online

# Set level acestor report; DB2 UDB 8.1.9 Linux

 From a table like the one below, is there an easy (ha ha) way to get a set level of pedigree (3 or 5 generations, say?). I have seen solutions on this group for all the ancestors, but, unless I misunderstood, no solution for a fixed number of generations. There is always the 2^n way join for n generations, but I'm looking for something a little simpler and not so fast growing in term of n. To make things easier? animal 0 is its own parents, and the parent of all animals whose parent(s) is(are) unknown. Right now, I am solving the three generation problem with a recursive php function, but I am looking for a non-procedural sql solution. create table t ( animal_id integer, sire_id integer, dam_id integer, namex varchar(40), , primary key (animal_id) ) alter table t add constraint sire_id foreign key(sire_id) references t(animal_id) alter table t add constraint dam_id foreign key(dam_id) references t(animal_id) Mar 29 '06 #1
• viewed: 1198
Share:
2 Replies
 In article , rs**********@charter.net says... From a table like the one below, is there an easy (ha ha) way to get a set level of pedigree (3 or 5 generations, say?). One of my favorite replies :) Download (and read) the DB2 UDB Cookbook from Graeme Birchall (page 299, recursive queries) from http://mysite.verizon.net/Graeme_Birchall/id1.html Mar 29 '06 #2
 Gert van der Kooij wrote: In article , rs**********@charter.net says... From a table like the one below, is there an easy (ha ha) way to get aset level of pedigree (3 or 5 generations, say?). One of my favorite replies :) Download (and read) the DB2 UDB Cookbook from Graeme Birchall (page 299, recursive queries) from http://mysite.verizon.net/Graeme_Birchall/id1.html Thank you for the reply/pointer. While I only read the recommended chapter, I would recommend it to any one using DB2. It is clearly written, with copious examples. The given solutions, as I read the chapter, contain 1 row for each individual in the ancestor tree. What if I want 1 row for the scion with all of the ancestors, perhaps in some given order? Mar 29 '06 #3

### This discussion thread is closed

Replies have been disabled for this discussion.