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),
<lots of other stuff>,
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)
