RM10 and the DNA tab
RM10 has a DNA tab on the person details where you can enter the shared cM with another. Great! It will show the relationship the the DNA suggests, and also the actual relationship between you and the match on your tree. Great! It can work out where (if anywhere) your tree doesn't line up with the DNA.
However, it doesn't show the ancestors which link you and this person together.
I am very pleased to say that I have created a SQL to pull out the said ancestoral names.
/*
Uses the data from the DNA tab in a person view.
Links the ancestors to the match. This information is not available from the front end.
*/
SELECT n.surname || ", " || n.given || " " || n.suffix as Match
,nt.surname || ", " || nt.given || " " || nt.suffix as With
,m.id2 as "Match RM"
,nat.surname || ", " || nat.given || " " || nat.suffix as "Male Ancestor"
,nta.surname || ", " || nta.given || " " || nta.suffix as "Female Ancestor"
,m.sharedcm as Sharing
,m.sharedsegs as Segments
,m.label2 as 'Match Anc code'
FROM dnatable m
join nametable n on m.id2=n.ownerid
join nametable nt on m.id1=nt.ownerid
join FamilyTable f on m.CommonAnc = f.familyID
join nametable nat on f.fatherid=nat.ownerid
join nametable nta on f.motherid=nta.ownerid
order by match
For anyone who is interested. I'm very chuffed with myself for this. Now I have the tools required to investigate DNA further
Congrats, Ali, on working this out yourself. As written, you may get an Alternate Name rather than the Primary Name for a person, depending on the order in which they were set. To restrict the results to exclusively the Primary Name, add to each of your joins with NameTable a constraint using the NameTable.IsPrimary field. For example:
join NameTable nta on f.MotherID=nta.OwnerID AND nta.IsPrimary
Thanks for that, both the priase and the suggestion. I hadn't thought of that...