Family Tree Branches

Okie, this one has me a little stumped so bear with me an I will explain as best I can...

I have a family tree database...

The two key tables (for this problem) are Person and Child. The person table is your generic person details table and contains, PersonId, PersonFirstName, PersonLastName, PersonDOB etc. The Child table is a linking table that links a PersonId to a ChildPersonId (so both Id's are valid Id's in the person table).

Now what I want to try and achieve is a breakdown of the different branchs of the family tree. A branch is an independant, unattached section of the tree (if that makes sense). It's a grouping of people we know are related but we can't determine how they are related to another group of people in the tree.

If you look at this http://gw.geneanet.org/index.php3?b=bengos&lang=en;m=N;v=van+halewyn you will get an idea of what I mean.

I'm not sure if this is something that can be don't with a query at all the be honest... I suspect that I will have to wrap some other code around it, but I'm really not sure on what approach I should be using. Any help people could offer would be great.Are you just looking for all the roots? If that's the case, this would show you that:

SELECT c1.PersonId
FROM
Child c1
LEFT OUTER JOIN Child c2 ON c1.PersonId = c2.ChildPersonId
WHERE c2.ChildPersonId IS NULL

This will give you the root of all branches. You can then build your individual branches from there. Make sense?|||linking table can be modified with an identity column for each parent-child
relation , so that any branch of tree can be identified with the identity column.|||Are you sure that is going to work...? It would find the root nodes but there could be multiple root nodes for a tree couldn't there? The family tree itself isn't really a tree structure... it expands in both directions from any given point.

It expands up (for example) due to the children nodes and up because of the parent nodes...|||currently what I am doing is

1. Getting all PersonIds and creating an array.
2. Take the first Id and get all the children and parent and spouse ids
3. Get all the child and parent and spouse id's of those found in step 2.
- repeat until no more ids are found, this means the end of a branch
4. go back to array and select the first id that is not one of those that has been found so far
5. return to step 2 until all ideas have been found or searched on

this gives me all the branches.... not sure how to structure the display be it's a start.|||Can u post the table structure?|||CREATE TABLE [dbo].[Child] (
[PersonId] [int] NULL ,
[ChildPersonId] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Person] (
[PersonId] [int] NOT NULL ,
[Firstnames] [nvarchar] (60) NULL ,
[Surname] [nvarchar] (60) NULL ,
[Title] [nvarchar] (100) NULL ,
[Gender] [nvarchar] (1) NULL ,
[DOBDay] [int] NULL ,
[DOBMonth] [int] NULL ,
[DOBYear] [int] NULL ,
[POBLocale] [nvarchar] (50) NULL ,
[POBCountry] [nvarchar] (50) NULL ,
[DODDay] [int] NULL ,
[DODMonth] [int] NULL ,
[DODYear] [int] NULL ,
[PODLocale] [nvarchar] (50) NULL ,
[PODCountry] [nvarchar] (50) NULL ,
[POBurial] [nvarchar] (50) NULL
) ON [PRIMARY]
GO

Now for the Child table both ChildPersonId and PersonId link to a valid PersonId in the Person table|||I would like to suggest 3 more columns in Child Table. RealtionId and ParentRealtionId
And RelationHierarchy.

CREATE TABLE [dbo].[Child] (
[RelationId] [int] NOT NULL,
[ParentRealtionId] NULL,
[PersonId] [int] NULL ,
[ChildPersonId] [int] NULL ,
[RelationHierarchy] varchar(4000)
) GO

so the tables look like

Person Table:

[PersonId] [Firstnames] [Surname]
-------------------
1 Gauthier van Halewyn ..

2 Rogier van Halewyn ..

3 Jacques van Halewyn..

4 Roland van Halewyn ..


Child Table:

[RelationId] [ParentRealtionId] [PersonId] [ChildPersonId] [RelationHierarchy]
-----------------------------
10 null null 1 null
20 10 1 2 10~
30 20 2 3 10~20~
40 30 3 4 10~20~30~

assumptions: Every PersonId occurs only once as a Child PersonId in Child Table.

So to find the children and grand children of a person
All u have to do is select the RelationHierarchy and RelationId from the child table
Concat both and search.

To find the family tree of Gauthier van Halewyn (PersonId=1)



DECLARE @. RelationHierarchy VARCHAR(4000)

SELECT @. RelationHierarchy= isnull(RelationHierarchy, ) + ~ + CONVERT(Varchar(20), RelationId) + ~% FROM [Child] WHERE [ChildPersonId]= 1

SELECT * FROM [Child ] WHERE RelationHierarchy LIKE @. RelationHierarchy

I'm not sure this is going to answer all your questions but this will definetly prove helpful.|||Hmmm,... I see what you are saying... I have though about that myself,.. adding a generation field or something somewhere... I don't want to complicate things too much though,.. but I think I might need to in order to achieve what I would like... thanks for the input