Which wifes died after giving birth?

  • Gerrit

    Gerrit - 2010-01-01

    Hi all! Happy 2010.

    I'm looking for a report or other tool which shows me all mothers who died after giving birth to their child. In some esotheric knowledge this information about your relatives is very important.

    Maybe somebody knows the datamodel of PHPGedview and has a sql-statement. Maybe somebody already made a report or knows of a report that shows this information.

    I would be very happy if somebody can share this information with us!

    TIA! Gerrit Bon.

  • Kerry Choy

    Kerry Choy - 2010-01-02

    Tricky. The only way I cold think of this being properly captured in the GEDCOM data would be in the Cause of Death.

        1 DEAT
        2 CAUS Childbirth.

    While a report to get this would be fairly easy to knock up, it would depend on your users consistently entering "Childbirth". There might be some other technical descriptions which might appear in a death certificate which you may also need to search for.

    Not sure what facility you had in mind to use a SQL statement? 

  • Greg Roach

    Greg Roach - 2010-01-02

    If you understand SQL, you could do this as follows:

    1) Identify births from the pgv_dates table

    2) Identify deaths from the pgv_dates table

    3) link these together using two instances of pgv_link (one link indi to fam as child, one link indi to fam as wife).

    You can get some examples to help wiht 1) and 2) from code in includes/classes/class_stats.php

  • Gerrit

    Gerrit - 2010-01-02

    Thanks for the answers so far. I'm the only user so for sure I didn't register the cause of death in a right manner. That leaves the sql-solution of fisharebest.
    I'm looking into the tables (don't have a datamodel at hand) and can't find pgv_link or is this a foreign-key? My sql-knowledge dates from 10 years back. I will look into the code.

    Thanks all. ( if somebody has a sql-statement ready, please let me know! )

  • Gerrit

    Gerrit - 2010-01-03

    So far I created the following sqls.
    Just to get the feeling again, I Started with retrieving all wives with at least 1 child:
    SELECT COUNT(f_wife) FROM pgv_families f
    WHERE f.f_wife != ''
    AND f.f_numchil > 0
    AND f.f_file = '3'

    RESULT: 1000 (exactly)

    Then, I wanted to find all mothers and their last child:

    SELECT f_wife AS mother, INSERT( SUBSTRING_INDEX( f_chil, 'I', -1 ) , 1, 0, 'I' ) AS last_child
    FROM pgv_families
    WHERE f_chil <> ''
    AND f_wife <> ''
    LIMIT 0 , 30

    All mothers with their last child:

    SELECT f_wife AS moeder, INSERT( SUBSTRING_INDEX( f_chil, 'I', -1 ) , 1, 0, 'I' ) AS laatste_kind
    FROM pgv_families
    WHERE f_chil <> ''
    AND f_wife <> ''
    LIMIT 0 , 30

    Now I have to find all mothers who died within a year after giving birth to their last child…

    Are there any comments about these queries? Please do so.

    PS in the preview all underscores vanish!

  • Christophe B.

    Christophe B. - 2010-01-03

    Interesting idea.
    I have uploaded a patch in PATCHES section.
    Feel free to use and modify.


  • Greg Roach

    Greg Roach - 2010-01-03

    The f_chil field doesn't have any ordering, so you cannot assume that the last entry is the last-born child.

    As I said previously, look in class_stats.php for examples of how to find birth/death dates.  Then use two instances of the pgv_link table to join the birth person to a family as CHIL/FAMC and the death person to the same family as a WIFE/FAMS

  • Gerrit

    Gerrit - 2010-01-03

    When I look in my database, there are 20 tables, there's NO table pgv_link . Do you mean a join in 1 sql or using a UNION ?

  • Greg Roach

    Greg Roach - 2010-01-03

    Which version of PGV.  IIRC the pgv_link table was added in 4.2.1 (April 2009)

  • Gerrit

    Gerrit - 2010-01-03

    Okay, that explains my misunderstanding, i've got an older version (not sure but think 4.2), cannot find the version-no. This means that I first update and the go further?

  • Greg Roach

    Greg Roach - 2010-01-03

    > This means that I first update and the
    > go further?

    If your server can support the latest version, then yes - you should upgrade.

    The link table was added to make it possible to run this sort of query in SQL.

  • Gerrit

    Gerrit - 2010-01-03

    Thanks for your help fisharebest! I'll need a couple of days to get the update done. See you later!



Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

No, thanks