Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
coxmg
Starting Member
16 Posts |
 Posted - 2011-11-25 : 13:56:43
|
| Hey guys, here's a unique problem I want to solve. I have my family tree and I want to create a database to house the information.I want to know what percentage of my family originated in what country. I want to write a query that will provide me with this information. However some branches only go back like 4 generations while others go back about 12-15 generations all the way into other countries. If that branch is back to the last 15 generations, then that percentage of the family tree should cover the percentage of my blood that is unknown all the way back to generation 15. For example, I have 2 parents, then 4 grandparents, then 8 great grandparents, then 16 great great grandparents, back all the way to 15 generations, which would give me a grand total of 32,768 possible ancestors at generation 15 immigrating from England, Ireland, Germany, or some unknown place.Any ideas on how to structure this to see where the generations ends? Basically I want the query to stop returning results when it reaches outside the unites states or possibly when that branch terminates with an "unknown" mother/father. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-11-26 : 09:19:22
|
This is not a complete solution, but just thoughts on how I might start going about it.1. Define a table where each person, and their father and mother, along with their country is listed. For example, like this:create table dbo.Parentage( person_id int, father_id int, mother_id int, country_of_origin varchar(32))insert into dbo.Parentage values(1,2,11,'USA'),(2,20,5,'UK'),(11,6,null,'IRE'),(20,7,8,'JPN'),(5,9,10,'AUS'); 2. Use a recursive CTE to calculate the information you want. The LVL in the CTE output shows how far removed from you the ancestor is. with cte as( select 1 as lvl, father_id as ancestor1, mother_id as ancestor2 from Parentage where person_id = 1 union all select lvl+1, p.father_id,p.mother_id from dbo.Parentage p inner join cte c on c.ancestor1 = p.person_id union all select lvl+1, p.father_id,p.mother_id from dbo.Parentage p inner join cte c on c.ancestor2 = p.person_id)select * from cte; The funny thing here is, I don't quite know if what I am doing here is allowed/will work right in all cases. I am using the recursive CTE with a single anchor (which is a requirement for recursive CTE's) and two recursive sections (which seems like should pose no problems) but I have not tried that before now. |
 |
|
|
coxmg
Starting Member
16 Posts |
Posted - 2011-11-30 : 11:19:09
|
| Thanks for the try. Question for you, is CTE an addon package? Do you have to pay for that to use it? I only have SQL Express the free version. I started this project in ACCESS SQL. Basically I tried doing it another way, using a manually entered generation # (obviously the user has to calculate this on his own). In the table below, fatherID and motherID are joined back to the person table, AncestorID in a one to many. For percent ethnicity, the further you go back, the less impact the generation member has on ones ethnicity, so I calculated this, for example one generation back is 50%, generation 10 is only .195% of my ethnicity. Let me know if anyone else has any other ideas for how to do this. Ancestor_IDFirst_NameLast_NameBirth_DateDeath_DateFather_IDMother_IDBirth_Place_CityBirth_Place_StateBirth_Place_CountryPresumed_OriginGeneration_ID |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-11-30 : 12:00:25
|
| CTE (Common table expressions) is not an add-on package. It is a T-SQL feature that is available in SQL 2005 and later. It is available in all SKUs, including Express.You should be able to copy the code I posted toSQL Server Management Studio and run it. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
coxmg
Starting Member
16 Posts |
Posted - 2011-11-30 : 18:15:21
|
| Hey guys basic question, should the father_id/ancestor_ID be 1 to 1 right? or should it be 1 to many? I'm assuming 1 person has 1 father and 1 mother. |
|
|
|
coxmg
Starting Member
16 Posts |
Posted - 2011-12-01 : 13:44:21
|
| Hey guy thanks for your code, it worked like a charm! I tried on SQL Server Express, it appears to set the relationship on its own. |
|
|
|
coxmg
Starting Member
16 Posts |
Posted - 2011-12-01 : 14:19:54
|
| Hey guy thanks for your code, it worked like a charm! I tried on SQL Server Express, it appears to set the relationship on its own. |
|
|
|
|
|
|
|
|
Family Tree in a Database Structure