SQL Loop over a family tree
- by simon831
Using SQL server 2008.
I have a family tree of animals stored in a table, and want to give some information on how 'genetically diverse' (or not) the offspring is.
In SQL how can I produce sensible metrics to show how closely related the parents are?
Perhaps some sort of percentage of shared blood, or a number of generations to go back before there is a shared ancestor?
AnimalTable
Id
Name
mumId
dadId
select * from AnimalTable child
inner join AnimalTable mum on child.[mumId] = mum.[Id]
inner join AnimalTable dad on child.[dadId] = dad.[Id]
inner join AnimalTable mums_mum on mum.[mumId] = mums_mum.[Id]
inner join AnimalTable mums_dad on mum.[dadId] = mums_dad.[Id]
inner join AnimalTable dads_mum on dad.[mumId] = dads_mum.[Id]
inner join AnimalTable dads_dad on dad.[dadId] = dads_dad.[Id]
