Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618

    Unanswered: 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=...;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.

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    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?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Mar 2004
    Posts
    80
    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.

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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....

  5. #5
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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.

  6. #6
    Join Date
    Mar 2004
    Posts
    80
    Can u post the table structure?

  7. #7
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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

  8. #8
    Join Date
    Mar 2004
    Posts
    80
    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.

  9. #9
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •