Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

Which wifes died after giving birth?

Gerrit
2010-01-01
2013-05-30
  • 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.

    Gbon
    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.

    Christophe

     
  • 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!

    gbon.