Difference between revisions of "Report: FindaGrave Citation Worklist"

From TNG_Wiki
Jump to navigation Jump to search
(Text replacement - "<syntaxhighlight lang="mysql" enclose="div">" to "<syntaxhighlight lang="mysql">")
 
(4 intermediate revisions by one other user not shown)
Line 1: Line 1:
−
This query that produces a report that shows the Individuals whose FindAGrave Citation and/or Burial Event Note do not conform to my specifications for FindAGrave citations and notes, as described in the [[FindAGrave Links]] mod article.
+
The [[FindAGrave Links]] mod provides a shorthand syntax (GRid={memorial number}) for  links to FindAGrave memorial pages in most notes (e.g. Events, Places, Cemeteries, Media). Its wiki article proposes a convention for using that syntax to place FindAGrave links in death or burial events and a citation's page reference field. This query (or rather these queuries) produce report that show people whose events and/or Citations do not contain FindAGrave links. Problematically, there is no way to be sure that a FindAGrave memorial even exists for any particular person, so people for whom no FindAGrave memoral exists will necessarily be included in the query results. (I've thought long and hard about a way to flag people for whom no FindAGrave memorial exists, but, so far, I haven't found a straightforward way to do so.)
  
−
This article contiains three versions of the query:
+
Parenthetically:
−
# One for phpMyAdmin,
 
−
# One complete query for the TNG Reporting Tool, and
 
−
# A version that omits a particular data entry provision that I utilize in my database.
 
  
−
To reduce the query's run time, I've included an INNER JOIN on the Branchlinks table to constrain the query to a specific TNG Branch that contains close relatives.  '''YOU'LL NEED TO MODIFY OR REMOVE THAT JOIN OR DEFINE THAT BRANCH IN YOUR DATABASE.'''<br>  
+
The shorthand expression for links to FindAGrave memorials can be used in many (perhaps all) notes field in a TNG database, such as marriage event notes, place notes, and s. place notes, and
 +
 
 +
This article contians three versions of the query:
 +
# A direct SQL query for the TNG Reporting Tool,
 +
# A simpler SQL query that works in tools such as phpMyAdmin, but not in the TNG Reporting tool, and
 +
# A variant that omits a particular provision that is assume byt tghe first two queries.
 +
 
 +
To reduce the query's run time and size of the result set, the queries include a SQL JOIN to a TNG branch that might contain "important" individuals, such as the ancestors and/or descendants of a particular indivual and to try to focus on "important" individuals, I've included a join to the Branchlinks table that constrain the query to a specific Branch that contains close relatives.  '''YOU'LL NEED TO MODIFY OR REMOVE THAT JOIN OR DEFINE THAT BRANCH IN YOUR DATABASE.'''<br>  
 
(My Branch rule parameters were: startPerson=my father, agens=15, dgens=4, dagens=1)
 
(My Branch rule parameters were: startPerson=my father, agens=15, dgens=4, dagens=1)
  
Line 13: Line 17:
  
 
== The SELECT clause ==
 
== The SELECT clause ==
−
* The "Parents" column contains the parents' last names if the parents are in the database, and contains a portion of the Birth Event note if the Birth Event note appears to list the parents' names. If you don't intend to use the Birth Event note to store the names of in-laws, then you can replace the 4-line expression that uses cascading IF functions with just<br>
+
* The "Parents" column contains the parents' last names if the parents are in the database, and contains a portion of the Birth Event note if the Birth Event note appears to list the parents' names. If you don't intend to use the Birth Event note to store the names of in-laws, then you can replace the 4-line expression that uses cascading IF functions with just<br>CONCAT(d.lastname,' & ',m.lastname) as Parents
−
CONCAT(d.lastname,' & ',m.lastname) as Parents
+
* The "Spouse" & Marriage Date ("marrdate") columns generate multiple mostly-redundant rows in the report if there are multiple spouses.
−
*The "Spouse" & Marriage Date ("marrdate") columns generate multiple mostly-redundant rows in the report if there are multiple spouses.
+
* The "Tag" column indicates which cited event the query found.
−
*The "Tag" column indicates which cited event the query found.
+
* The "Place" column is the place associated with the Tag.
−
*The "Place" column is the place associated with the Tag.
 
 
* The Event note is the note associated with the Tag, and it is supposed to contain "GRid=nnnn" plus a description of the FindAGrave page.
 
* The Event note is the note associated with the Tag, and it is supposed to contain "GRid=nnnn" plus a description of the FindAGrave page.
 
* Citation fields: citetext and page. Remember that these are the only Citations data fields (that is, excluding the foreign keys to the sourceID and the personID/eventID that survive the Ancestry.com  >> FTM >> gedcom >> TNG interface.
 
* Citation fields: citetext and page. Remember that these are the only Citations data fields (that is, excluding the foreign keys to the sourceID and the personID/eventID that survive the Ancestry.com  >> FTM >> gedcom >> TNG interface.
 
* Source Title - which is really pretty useless. We really don't care which FindAGrave source is used.  In fact, I actually use my Gedcom Converter mod to merge all FindAGrave sources into one source titled 'FindAGrave.com'
 
* Source Title - which is really pretty useless. We really don't care which FindAGrave source is used.  In fact, I actually use my Gedcom Converter mod to merge all FindAGrave sources into one source titled 'FindAGrave.com'
−
* Why - This expression, made up of cascading IF functions, is intended to give the user (or more particularly, me, a sense of which error condition caused this record to be included in the query result.  This expression will only find one error condition, even if the record has several.
+
* Why - This expression, made up of cascading IF functions, is intended to give the user (or more particularly, me, a sense of which error condition caused this record to be included in the query result.  This expression will only find one error condition, even if the record has several. The error conditions it finds, in order, are:
−
The error conditions it finds, in order, are:
 
 
*# page: The Citation Page field does not contain a GRid number or is too short to include the GRID and the person's name.
 
*# page: The Citation Page field does not contain a GRid number or is too short to include the GRID and the person's name.
 
*# note: The burial or death event note does not contain a GRid number or is too short to include a description of the FindAGrave page.
 
*# note: The burial or death event note does not contain a GRid number or is too short to include a description of the FindAGrave page.
Line 32: Line 34:
 
Or "How this query finds records and events (and why)"
 
Or "How this query finds records and events (and why)"
  
−
***BOB: These next two paragraphs may be redundant with the Recording FindAGrave References section of the FindAGrave Links article ***
+
<!-- **** BOB: These next two paragraphs may be redundant with the Recording FindAGrave References section of the FindAGrave Links article **** -->
−
In Ancestry.com and Family Tree Maker, and in many other genealogy applications, one Source
+
???In Ancestry.com and Family Tree Maker, and in many other genealogy applications, one Source
 
Citation can be tied to multiple events - even in multiple records. But in the Gedcom model
 
Citation can be tied to multiple events - even in multiple records. But in the Gedcom model
 
and in TNG, that's not the case. In TNG, each event that is supported by a particular FindAGrave  
 
and in TNG, that's not the case. In TNG, each event that is supported by a particular FindAGrave  
 
Memorial has a separate Citation record.  However, all of the citations associated with a particular Memorial are probably identical, except for the EventID.  
 
Memorial has a separate Citation record.  However, all of the citations associated with a particular Memorial are probably identical, except for the EventID.  
  
−
But in Ancestry.com and Family Tree Maker (FTM), and many other genealogy database programs, one Source Citation can be tied to multiple events.  
+
But in Ancestry.com and Family Tree Maker (FTM), and many other genealogy database programs, one Source Citation can be tied to multiple events. ???
−
***
+
 
 
This TNG query generates a workslist that is intended to help the user edit People and Citations  
 
This TNG query generates a workslist that is intended to help the user edit People and Citations  
 
in a genealogy application that <i>shares</i> one Source Citations among the multiple events.
 
in a genealogy application that <i>shares</i> one Source Citations among the multiple events.
Line 62: Line 64:
  
 
So, my contrived Sources to Citations join is accomplished through this inner query:
 
So, my contrived Sources to Citations join is accomplished through this inner query:
−
<syntaxhighlight lang="mysql" enclose="div">
+
<syntaxhighlight lang="mysql">
−
   (SELECT sourceID, gedcom, persfamID,<br>
+
   (SELECT sourceID, gedcom, persfamID, MIN(eventID) AS tag, page, citetext  
−
        MIN(IF(eventID='BURI','0B',IF(eventID='DEAT','0D',eventID))) AS tag,<br>
+
     FROM tng_citations c
−
        page, citetext <br>
+
    WHERE eventID = 'BURI' OR eventID = 'DEAT'
−
     FROM tng_citations c <br>
+
     GROUP BY sourceID,gedcom) AS q1 ON q1.sourceID=s.sourceID AND q1.gedcom=s.gedcom
−
     GROUP BY sourceID,gedcom) AS q1 ON q1.sourceID=s.sourceID AND q1.gedcom=s.gedcom<br>
 
 
</syntaxhighlight>
 
</syntaxhighlight>
 
where I get the Burial Event citation if there is one, or a Death Event Citation (if there is one), or any other arbitrary event citation if there is no Burial or Death Event Citation.
 
where I get the Burial Event citation if there is one, or a Death Event Citation (if there is one), or any other arbitrary event citation if there is no Burial or Death Event Citation.
Line 76: Line 77:
 
'BURI' and 'DEAT' aren't really eventIDs, but since Burial and Death (and Birth and Name, or for that manner) are, to TNG, "built-in" events, the event data is stored in the People record instead of an Event record, and the eventID field is a "built-in" event ''tag'' instead of an eventID.  (FWIW, this is the very reason that Citations have to store the personID instead of just the eventID.)
 
'BURI' and 'DEAT' aren't really eventIDs, but since Burial and Death (and Birth and Name, or for that manner) are, to TNG, "built-in" events, the event data is stored in the People record instead of an Event record, and the eventID field is a "built-in" event ''tag'' instead of an eventID.  (FWIW, this is the very reason that Citations have to store the personID instead of just the eventID.)
  
−
<!-- **** Bob: Here's another technical digression that belongs somewhere else in the article - or maybe in the Recording FindAGrave References section of the FindAGrave Links mod article ***** -->
+
<!-- **** Bob: Here's another technical digression that belongs somewhere else in the article - or maybe in the Recording FindAGrave References section of the FindAGrave Links mod article **** -->
−
Annoyingly, Ancestry.com and FTM treat "Arkansas Find A Grave Index" and "U.S. Find A Grave Index" as separate sources, even though the "U.S. Find A Grave Index" contains every single "Arkansas Find A Grave Index" record with excactly the same Grave ID's.  So it's possible for a Person to have Event Citations that connect to more than one Find A Grave source.  If that's the case, this query will list the personID for each FindAGrave source.  When the user doing data entry encounters the first of the duplicate personID's, the user should see that there is more than one FindAGrave citation, and delete one of them. Then, when the user encounters the same personID a second time, the user will simply see a Person record that has already been cleaned up.
+
???Annoyingly, Ancestry.com and FTM treat "Arkansas Find A Grave Index" and "U.S. Find A Grave Index" as separate sources, even though the "U.S. Find A Grave Index" contains every single "Arkansas Find A Grave Index" record with excactly the same Grave ID's.  So it's possible for a Person to have Event Citations that connect to more than one Find A Grave source.  If that's the case, this query will list the personID for each FindAGrave source.  When the user doing data entry encounters the first of the duplicate personID's, the user should see that there is more than one FindAGrave citation, and delete one of them. Then, when the user encounters the same personID a second time, the user will simply see a Person record that has already been cleaned up.???
−
***
+
 
 
=== Logical Join to People ===
 
=== Logical Join to People ===
 
Here's where I take advantage of the Branch Index to restrict the number of People found by the query.  So this "logical join" is two joins; to BranchLinks, and then to People.
 
Here's where I take advantage of the Branch Index to restrict the number of People found by the query.  So this "logical join" is two joins; to BranchLinks, and then to People.
  
 
=== Logical Join to the Parents' Records ===
 
=== Logical Join to the Parents' Records ===
−
I want the parents' names only because, sometimes, I don't already have both parents, and sometimes, the FindAGrave record lists the parents.  
+
I want to show the parents' names because, sometimes, I don't already have both parents, and sometimes, the FindAGrave record lists the parents.  
  
−
At this point, I have to start using LEFT JOINs, because I can't be sure that there ''are'' any parents for this person.  To get the parents' names, I need to join People to Children, then from Children to the parent's Family record, and then to the dad's record, and the mom's record.
+
At this point, I have to start using LEFT JOINs, because I can't be sure that there ''are'' any parents for this person.  To get the parents' names, I need to join People to Children, then join Children to the parent's Family record, and then join to the father's record, and the mother's record.
  
 
=== Logical Join to Spouses ===
 
=== Logical Join to Spouses ===
−
The purpose of the Spouse, and MarrDate columns, as with the parents' names, is to reveal whether any spouse's names or marriage data in the FindAGrave record are of interest.
+
The purpose of the Spouse and MarrDate columns, as with the parents' names, is to reveal whether any spouse's names or marriage data in the FindAGrave record are of interest. Getting the spouses is straightforward - just join the Person record to Families records in which this person is a spouse, and join to the People records for the ''other'' spouse.
−
 
+
<syntaxhighlight lang="mysql">
−
Getting the spouses is straightforward - just join to Families record in which this person is a spouse, and join to the People record for the ''other'' spouse.
+
LEFT JOIN tng_families fams
−
find the other spouse.
+
    ON fams.wife=p.personID OR fams.husband=p.personID AND fams.gedcom=p.gedcom
 +
LEFT JOIN tng_people sp
 +
    ON (IF(fams.wife=p.personID,sp.personID=fams.husband,sp.personID=fams.wife))
 +
      AND sp.gedcom=p.gedcom
 +
</syntaxhighlight>
  
 
=== Logical Join to the Birth Note ===
 
=== Logical Join to the Birth Note ===
 
Fortunately, the Notelinks table uses the built-in event Tag name and personID in the same way that the Citations table does, so we can just join to Notelinks where eventID='BIRT' and then to the related xnotes record, which contains the note itself.
 
Fortunately, the Notelinks table uses the built-in event Tag name and personID in the same way that the Citations table does, so we can just join to Notelinks where eventID='BIRT' and then to the related xnotes record, which contains the note itself.
−
 
+
<syntaxhighlight lang="mysql">
−
=== Logical Join to the Burial or Event Note ===
 
−
We can use the Tag Fortunately, the Notelinks table uses the built-in event Tag name and personID in the same way that the Citations table does, so we can just join to Notelinks where eventID='BIRT' and then to the related xnotes record, which contains the note itself.
 
−
<syntaxhighlight lang="mysql" enclose="div">
 
 
LEFT JOIN tng_notelinks n1 ON p.personid = n1.persfamID  
 
LEFT JOIN tng_notelinks n1 ON p.personid = n1.persfamID  
 
       AND n1.gedcom = p.gedcom AND n1.eventID = 'BIRT'  
 
       AND n1.gedcom = p.gedcom AND n1.eventID = 'BIRT'  
 
LEFT JOIN tng_xnotes x1 ON x1.id = n1.xnoteid AND x1.gedcom = p.gedcom
 
LEFT JOIN tng_xnotes x1 ON x1.id = n1.xnoteid AND x1.gedcom = p.gedcom
−
/* Table aliases n2 and x2 get us the note associated with the Event that we found */
+
</syntaxhighlight>
−
LEFT JOIN tng_notelinks n2 ON p.personid = n2.persfamID  
+
 
−
      AND n2.gedcom = p.gedcom  
+
=== Logical Join to the Burial or Death Note ===
−
       AND n2.eventID = IF(q1.tag='0B', 'BURI', IF(q1.tag='0D', 'DEAT', q1.tag))
+
Using the same Join construct as the Birth note, we can use the Tag that we found in the citation above to link to the Burial or Death Note.
 +
<syntaxhighlight lang="mysql">
 +
LEFT JOIN tng_notelinks n2 ON p.personid = n2.persfamID AND n2.gedcom = p.gedcom  
 +
       AND n2.eventID = q1.tag  
 
LEFT JOIN tng_xnotes x2 ON x2.id = n2.xnoteid AND x2.gedcom = p.gedcom
 
LEFT JOIN tng_xnotes x2 ON x2.id = n2.xnoteid AND x2.gedcom = p.gedcom
 
</syntaxhighlight>
 
</syntaxhighlight>
Line 112: Line 117:
 
This query is suitable for running in phpMyAdmin. See below for the query that will run in the TNG Reporting tool.  The SELECT clause comments in this query would confuse the TNG Reporting Tool.
 
This query is suitable for running in phpMyAdmin. See below for the query that will run in the TNG Reporting tool.  The SELECT clause comments in this query would confuse the TNG Reporting Tool.
  
−
<syntaxhighlight lang="mysql" enclose="div">
+
<syntaxhighlight lang="mysql">
 
SELECT p.personID, p.birthdatetr, CONCAT(p.firstname,' ',p.lastname,' ',p.suffix) AS Name,
 
SELECT p.personID, p.birthdatetr, CONCAT(p.firstname,' ',p.lastname,' ',p.suffix) AS Name,
 
     /* Parents' names may come from linked database records or from Birth notes */
 
     /* Parents' names may come from linked database records or from Birth notes */
Line 122: Line 127:
 
     CONCAT(sp.firstname,' ',sp.lastname) AS Spouse, fams.marrdate,
 
     CONCAT(sp.firstname,' ',sp.lastname) AS Spouse, fams.marrdate,
 
     /* The Built-in Event Tag that we are focusing on */
 
     /* The Built-in Event Tag that we are focusing on */
−
     IF(q1.tag='0B','BURI', IF(q1.tag='0D', 'DEAT', q1.tag)) AS Tag,  
+
     q1.tag,  
 
     /* The Place associated with that Built-in Event */
 
     /* The Place associated with that Built-in Event */
−
     IF(q1.tag='0B', p.burialplace, IF(q1.tag='0D',p.deathplace ,p.birthplace)) AS Place,
+
     IF(q1.tag='BURI', p.burialplace, IF(q1.tag='DEAT',p.deathplace, p.birthplace)) AS Place,
 
     /* Show at most 80 characters of the EventNote */
 
     /* Show at most 80 characters of the EventNote */
 
     IF(LENGTH(x2.note)>80, CONCAT(LEFT(x2.note,80),'&hellip;'), x2.note) AS EventNote,
 
     IF(LENGTH(x2.note)>80, CONCAT(LEFT(x2.note,80),'&hellip;'), x2.note) AS EventNote,
Line 147: Line 152:
 
     /* the Burial event if it has a FindAGrave citation. If there is no Burial event, we'll */
 
     /* the Burial event if it has a FindAGrave citation. If there is no Burial event, we'll */
 
     /* get the Death event, and then other event tags in arbitrary alpha order */
 
     /* get the Death event, and then other event tags in arbitrary alpha order */
−
    (SELECT sourceID, gedcom, persfamID,
+
  (SELECT sourceID, gedcom, persfamID, MIN(eventID) AS tag, page, citetext  
−
        MIN(IF(eventID='BURI','0B',IF(eventID='DEAT','0D',eventID))) AS tag,
+
     FROM tng_citations c
−
        page, citetext  
+
    WHERE eventID = 'BURI' OR eventID = 'DEAT'
−
     FROM tng_citations c  
 
 
     GROUP BY sourceID,gedcom) AS q1 ON q1.sourceID=s.sourceID AND q1.gedcom=s.gedcom
 
     GROUP BY sourceID,gedcom) AS q1 ON q1.sourceID=s.sourceID AND q1.gedcom=s.gedcom
 
/* Use the Branchlinks table to speed up the query and restrict the number of matches */
 
/* Use the Branchlinks table to speed up the query and restrict the number of matches */
Line 176: Line 180:
 
LEFT JOIN tng_xnotes x1 ON x1.id = n1.xnoteid AND x1.gedcom = p.gedcom
 
LEFT JOIN tng_xnotes x1 ON x1.id = n1.xnoteid AND x1.gedcom = p.gedcom
 
/* Table aliases n2 and x2 get us the note associated with the Event that we found */
 
/* Table aliases n2 and x2 get us the note associated with the Event that we found */
−
LEFT JOIN tng_notelinks n2 ON p.personid = n2.persfamID  
+
LEFT JOIN tng_notelinks n2 ON p.personid = n2.persfamID AND n2.gedcom = p.gedcom  
−
      AND n2.gedcom = p.gedcom  
+
       AND n2.eventID = q1.tag,
−
       AND n2.eventID = IF(q1.tag='0B', 'BURI', IF(q1.tag='0D', 'DEAT', q1.tag))
 
 
LEFT JOIN tng_xnotes x2 ON x2.id = n2.xnoteid AND x2.gedcom = p.gedcom
 
LEFT JOIN tng_xnotes x2 ON x2.id = n2.xnoteid AND x2.gedcom = p.gedcom
  
Line 206: Line 209:
 
* Have a default tree defined in your database.
 
* Have a default tree defined in your database.
  
−
<syntaxhighlight lang="mysql" enclose="div">
+
<syntaxhighlight lang="mysql">
 
SELECT p.personID, p.birthdatetr, CONCAT(p.firstname,' ',p.lastname,' ',p.suffix) AS Name,
 
SELECT p.personID, p.birthdatetr, CONCAT(p.firstname,' ',p.lastname,' ',p.suffix) AS Name,
 
     IF (d.lastname<>'',CONCAT(d.lastname,' & ',m.lastname),
 
     IF (d.lastname<>'',CONCAT(d.lastname,' & ',m.lastname),
Line 213: Line 216:
 
           IF (LOCATE('Dau of ',x1.note), MID(x1.note, Locate('Dau of ',x1.note)+7, 20), '')))) AS Parents,
 
           IF (LOCATE('Dau of ',x1.note), MID(x1.note, Locate('Dau of ',x1.note)+7, 20), '')))) AS Parents,
 
     CONCAT(sp.firstname,' ',sp.lastname) AS Spouse, fams.marrdate,
 
     CONCAT(sp.firstname,' ',sp.lastname) AS Spouse, fams.marrdate,
−
     IF(q1.tag='0B','BURI', IF(q1.tag='0D', 'DEAT', q1.tag)) AS Tag,  
+
     q1.tag,  
−
     IF(q1.tag='0B', p.burialplace, IF(q1.tag='0D',p.deathplace ,p.birthplace)) AS Place,
+
     IF(q1.tag='BURI', p.burialplace, IF(q1.tag='DEAT',p.deathplace ,p.birthplace)) AS Place,
 
     REPLACE( REPLACE( IF( LENGTH(x2.note)>80, CONCAT(LEFT(x2.note,80),'&hellip;'), x2.note), '\n','<br>'), '\r','<br>') AS EventNote,
 
     REPLACE( REPLACE( IF( LENGTH(x2.note)>80, CONCAT(LEFT(x2.note,80),'&hellip;'), x2.note), '\n','<br>'), '\r','<br>') AS EventNote,
 
     REPLACE( REPLACE( IF(LENGTH(citetext)>50, REPLACE(CONCAT(LEFT(citetext,50),'&hellip;'), citetext), '\n','<br>'), '\r','<br>') AS CiteTxt,
 
     REPLACE( REPLACE( IF(LENGTH(citetext)>50, REPLACE(CONCAT(LEFT(citetext,50),'&hellip;'), citetext), '\n','<br>'), '\r','<br>') AS CiteTxt,
Line 230: Line 233:
 
     /* the Burial event if it has a FindAGrave citation. If there is no Burial event, we'll */
 
     /* the Burial event if it has a FindAGrave citation. If there is no Burial event, we'll */
 
     /* get the Death event, and then other event tags in arbitrary alpha order */
 
     /* get the Death event, and then other event tags in arbitrary alpha order */
−
    (SELECT sourceID, gedcom, persfamID,
+
  (SELECT sourceID, gedcom, persfamID, MIN(eventID) AS tag, page, citetext  
−
        MIN(IF(eventID='BURI','0B',IF(eventID='DEAT','0D',eventID))) AS tag,
+
     FROM tng_citations c
−
        page, citetext  
+
    WHERE eventID = 'BURI' OR eventID = 'DEAT'
−
     FROM tng_citations c  
 
 
     GROUP BY sourceID,gedcom) AS q1 ON q1.sourceID=s.sourceID AND q1.gedcom=s.gedcom
 
     GROUP BY sourceID,gedcom) AS q1 ON q1.sourceID=s.sourceID AND q1.gedcom=s.gedcom
 
/* Use the Branchlinks table to speed up the query and restrict the number of matches */
 
/* Use the Branchlinks table to speed up the query and restrict the number of matches */
Line 259: Line 261:
 
LEFT JOIN tng_xnotes x1 ON x1.id = n1.xnoteid AND x1.gedcom = p.gedcom
 
LEFT JOIN tng_xnotes x1 ON x1.id = n1.xnoteid AND x1.gedcom = p.gedcom
 
/* Table aliases n2 and x2 get us the note associated with the Event that we found */
 
/* Table aliases n2 and x2 get us the note associated with the Event that we found */
−
LEFT JOIN tng_notelinks n2 ON p.personid = n2.persfamID  
+
LEFT JOIN tng_notelinks n2 ON p.personid = n2.persfamID AND n2.gedcom = p.gedcom  
−
      AND n2.gedcom = p.gedcom  
+
       AND n2.eventID = q1.tag  
−
       AND n2.eventID = IF(q1.tag='0B', 'BURI', IF(q1.tag='0D', 'DEAT', q1.tag))
 
 
LEFT JOIN tng_xnotes x2 ON x2.id = n2.xnoteid AND x2.gedcom = p.gedcom
 
LEFT JOIN tng_xnotes x2 ON x2.id = n2.xnoteid AND x2.gedcom = p.gedcom
  
Line 282: Line 283:
 
This query would be most appropriate if you do not use the Birth note to identify the parents when you choose not to create database records for the parents.
 
This query would be most appropriate if you do not use the Birth note to identify the parents when you choose not to create database records for the parents.
  
−
<syntaxhighlight lang="mysql" enclose="div">
+
<syntaxhighlight lang="mysql">
 
SELECT p.personID, p.birthdatetr, CONCAT(p.firstname,' ',p.lastname,' ',p.suffix) AS Name,
 
SELECT p.personID, p.birthdatetr, CONCAT(p.firstname,' ',p.lastname,' ',p.suffix) AS Name,
 
     CONCAT(d.lastname,' & ',m.lastname) AS Parents,
 
     CONCAT(d.lastname,' & ',m.lastname) AS Parents,
 
     CONCAT(sp.firstname,' ',sp.lastname) AS Spouse, fams.marrdate,
 
     CONCAT(sp.firstname,' ',sp.lastname) AS Spouse, fams.marrdate,
−
     IF(q1.tag='0B','BURI', IF(q1.tag='0D', 'DEAT', q1.tag)) AS Tag,  
+
     q1.tag,  
−
     IF(q1.tag='0B', p.burialplace, IF(q1.tag='0D',p.deathplace ,p.birthplace)) AS Place,
+
     IF(q1.tag='BURI', p.burialplace, IF(q1.tag='DEAT',p.deathplace ,p.birthplace)) AS Place,
 
     REPLACE( REPLACE( IF( LENGTH(x2.note)>80, CONCAT(LEFT(x2.note,80),'&hellip;'), x2.note), '\n','<br>'), '\r','<br>') AS EventNote,
 
     REPLACE( REPLACE( IF( LENGTH(x2.note)>80, CONCAT(LEFT(x2.note,80),'&hellip;'), x2.note), '\n','<br>'), '\r','<br>') AS EventNote,
 
     REPLACE( REPLACE( IF(LENGTH(citetext)>50, REPLACE(CONCAT(LEFT(citetext,50),'&hellip;'), citetext), '\n','<br>'), '\r','<br>') AS CiteTxt,
 
     REPLACE( REPLACE( IF(LENGTH(citetext)>50, REPLACE(CONCAT(LEFT(citetext,50),'&hellip;'), citetext), '\n','<br>'), '\r','<br>') AS CiteTxt,
Line 303: Line 304:
 
     /* the Burial event if it has a FindAGrave citation. If there is no Burial event, we'll */
 
     /* the Burial event if it has a FindAGrave citation. If there is no Burial event, we'll */
 
     /* get the Death event, and then other event tags in arbitrary alpha order */
 
     /* get the Death event, and then other event tags in arbitrary alpha order */
−
    (SELECT sourceID, gedcom, persfamID,
+
  (SELECT sourceID, gedcom, persfamID, MIN(eventID) AS tag, page, citetext  
−
        MIN(IF(eventID='BURI','0B',IF(eventID='DEAT','0D',eventID))) AS tag,
+
     FROM tng_citations c
−
        page, citetext  
+
    WHERE eventID = 'BURI' OR eventID = 'DEAT'
−
     FROM tng_citations c  
 
 
     GROUP BY sourceID,gedcom) AS q1 ON q1.sourceID=s.sourceID AND q1.gedcom=s.gedcom
 
     GROUP BY sourceID,gedcom) AS q1 ON q1.sourceID=s.sourceID AND q1.gedcom=s.gedcom
 
/* Use the Branchlinks table to speed up the query and restrict the number of matches */
 
/* Use the Branchlinks table to speed up the query and restrict the number of matches */
Line 332: Line 332:
 
LEFT JOIN tng_xnotes x1 ON x1.id = n1.xnoteid AND x1.gedcom = p.gedcom
 
LEFT JOIN tng_xnotes x1 ON x1.id = n1.xnoteid AND x1.gedcom = p.gedcom
 
/* Table aliases n2 and x2 get us the note associated with the Event that we found */
 
/* Table aliases n2 and x2 get us the note associated with the Event that we found */
−
LEFT JOIN tng_notelinks n2 ON p.personid = n2.persfamID  
+
LEFT JOIN tng_notelinks n2 ON p.personid = n2.persfamID AND n2.gedcom = p.gedcom  
−
      AND n2.gedcom = p.gedcom  
+
       AND n2.eventID = q1.tag  
−
       AND n2.eventID = IF(q1.tag='0B', 'BURI', IF(q1.tag='0D', 'DEAT', q1.tag))
 
 
LEFT JOIN tng_xnotes x2 ON x2.id = n2.xnoteid AND x2.gedcom = p.gedcom
 
LEFT JOIN tng_xnotes x2 ON x2.id = n2.xnoteid AND x2.gedcom = p.gedcom
  

Latest revision as of 16:45, 5 October 2023

The FindAGrave Links mod provides a shorthand syntax (GRid={memorial number}) for links to FindAGrave memorial pages in most notes (e.g. Events, Places, Cemeteries, Media). Its wiki article proposes a convention for using that syntax to place FindAGrave links in death or burial events and a citation's page reference field. This query (or rather these queuries) produce report that show people whose events and/or Citations do not contain FindAGrave links. Problematically, there is no way to be sure that a FindAGrave memorial even exists for any particular person, so people for whom no FindAGrave memoral exists will necessarily be included in the query results. (I've thought long and hard about a way to flag people for whom no FindAGrave memorial exists, but, so far, I haven't found a straightforward way to do so.)

Parenthetically:

The shorthand expression for links to FindAGrave memorials can be used in many (perhaps all) notes field in a TNG database, such as marriage event notes, place notes, and s. place notes, and

This article contians three versions of the query:

  1. A direct SQL query for the TNG Reporting Tool,
  2. A simpler SQL query that works in tools such as phpMyAdmin, but not in the TNG Reporting tool, and
  3. A variant that omits a particular provision that is assume byt tghe first two queries.

To reduce the query's run time and size of the result set, the queries include a SQL JOIN to a TNG branch that might contain "important" individuals, such as the ancestors and/or descendants of a particular indivual and to try to focus on "important" individuals, I've included a join to the Branchlinks table that constrain the query to a specific Branch that contains close relatives. YOU'LL NEED TO MODIFY OR REMOVE THAT JOIN OR DEFINE THAT BRANCH IN YOUR DATABASE.
(My Branch rule parameters were: startPerson=my father, agens=15, dgens=4, dagens=1)

Recording FindALink References

The FindAGrave Links mod article describes how I Record FindAGrave References article, and how I use the FindAGrave Widget (which is installed by that mod) to edit Person and Source Citation records in Ancestry.com and Family Tree Maker.

The SELECT clause

  • The "Parents" column contains the parents' last names if the parents are in the database, and contains a portion of the Birth Event note if the Birth Event note appears to list the parents' names. If you don't intend to use the Birth Event note to store the names of in-laws, then you can replace the 4-line expression that uses cascading IF functions with just
    CONCAT(d.lastname,' & ',m.lastname) as Parents
  • The "Spouse" & Marriage Date ("marrdate") columns generate multiple mostly-redundant rows in the report if there are multiple spouses.
  • The "Tag" column indicates which cited event the query found.
  • The "Place" column is the place associated with the Tag.
  • The Event note is the note associated with the Tag, and it is supposed to contain "GRid=nnnn" plus a description of the FindAGrave page.
  • Citation fields: citetext and page. Remember that these are the only Citations data fields (that is, excluding the foreign keys to the sourceID and the personID/eventID that survive the Ancestry.com >> FTM >> gedcom >> TNG interface.
  • Source Title - which is really pretty useless. We really don't care which FindAGrave source is used. In fact, I actually use my Gedcom Converter mod to merge all FindAGrave sources into one source titled 'FindAGrave.com'
  • Why - This expression, made up of cascading IF functions, is intended to give the user (or more particularly, me, a sense of which error condition caused this record to be included in the query result. This expression will only find one error condition, even if the record has several. The error conditions it finds, in order, are:
    1. page: The Citation Page field does not contain a GRid number or is too short to include the GRID and the person's name.
    2. note: The burial or death event note does not contain a GRid number or is too short to include a description of the FindAGrave page.
    3. xBir: The Citation Text field does not contain the string "Birth:", which should always be part of the text that is copied from the FindAGrave page to the Citation Text field.
    4. xDea: The Citation Text field does not contain the string "Death:", which should always be part of the text that is copied from the FindAGrave page to the Citation Text field.
    5. Spc: The burial or death event note contains multiple spaces, which were put there in error by someone doing FindAGrave Cleanup. Instead of hitting [enter] to start a new line, a data entry person hit the space bar until the upcoming text wrapped to a new line in the Ancestry.com Event Description window.

The FROM clause

Or "How this query finds records and events (and why)"

???In Ancestry.com and Family Tree Maker, and in many other genealogy applications, one Source Citation can be tied to multiple events - even in multiple records. But in the Gedcom model and in TNG, that's not the case. In TNG, each event that is supported by a particular FindAGrave Memorial has a separate Citation record. However, all of the citations associated with a particular Memorial are probably identical, except for the EventID.

But in Ancestry.com and Family Tree Maker (FTM), and many other genealogy database programs, one Source Citation can be tied to multiple events. ???

This TNG query generates a workslist that is intended to help the user edit People and Citations in a genealogy application that shares one Source Citations among the multiple events.

I start with Sources that have the word "grave" in their title. (If you want to avoid non- FindAGrave sources whose titles contain the word 'grave', you probably should look for both 'FindAGrave' and 'Find A Grave' in the title.)

Logical JOIN to Citations

To get from a Source to People, I have to go through the Citations table. As I noted above, in TNG, there are likely to be several duplicate Citations that tie a FindAGrave source to several Events in on Person's record, and each of those citations connects the Source to the Citation.

If I simply join Sources to Citations on citation.sourceID=source.sourceID, and then join that result to People on people.personID=citation.peopleID, I'll get a separate result row for each event that is tied to a FindAGrave citation.

So I need to contrive the Sources to Citations join to give me just one citation record.

Ostensibly, I could use "LIMIT 1" or select MIN(citationID) to get an arbitrary single Citation row. But since I use the Burial or Death Event description field to 1) Link to the FindAGRave page, and 2) Describe the FindAGrave page, I really want the Burial Event Citation (if there one) or the Death Event Citation (if there's no Burial Event Citation) so that I can grab the corresponding Event Note.

So, my contrived Sources to Citations join is accomplished through this inner query: 

   (SELECT sourceID, gedcom, persfamID, MIN(eventID) AS tag, page, citetext 
     FROM tng_citations c
     WHERE eventID = 'BURI' OR eventID = 'DEAT'
     GROUP BY sourceID,gedcom) AS q1 ON q1.sourceID=s.sourceID AND q1.gedcom=s.gedcom

where I get the Burial Event citation if there is one, or a Death Event Citation (if there is one), or any other arbitrary event citation if there is no Burial or Death Event Citation.

Note that, by convention, I use the table alias 'q1' rather than 'c' for what is effectively the Citations table because I used an inner query to get that result.

A brief technical digression:
'BURI' and 'DEAT' aren't really eventIDs, but since Burial and Death (and Birth and Name, or for that manner) are, to TNG, "built-in" events, the event data is stored in the People record instead of an Event record, and the eventID field is a "built-in" event tag instead of an eventID. (FWIW, this is the very reason that Citations have to store the personID instead of just the eventID.)

???Annoyingly, Ancestry.com and FTM treat "Arkansas Find A Grave Index" and "U.S. Find A Grave Index" as separate sources, even though the "U.S. Find A Grave Index" contains every single "Arkansas Find A Grave Index" record with excactly the same Grave ID's. So it's possible for a Person to have Event Citations that connect to more than one Find A Grave source. If that's the case, this query will list the personID for each FindAGrave source. When the user doing data entry encounters the first of the duplicate personID's, the user should see that there is more than one FindAGrave citation, and delete one of them. Then, when the user encounters the same personID a second time, the user will simply see a Person record that has already been cleaned up.???

Logical Join to People

Here's where I take advantage of the Branch Index to restrict the number of People found by the query. So this "logical join" is two joins; to BranchLinks, and then to People.

Logical Join to the Parents' Records

I want to show the parents' names because, sometimes, I don't already have both parents, and sometimes, the FindAGrave record lists the parents.

At this point, I have to start using LEFT JOINs, because I can't be sure that there are any parents for this person. To get the parents' names, I need to join People to Children, then join Children to the parent's Family record, and then join to the father's record, and the mother's record.

Logical Join to Spouses

The purpose of the Spouse and MarrDate columns, as with the parents' names, is to reveal whether any spouse's names or marriage data in the FindAGrave record are of interest. Getting the spouses is straightforward - just join the Person record to Families records in which this person is a spouse, and join to the People records for the other spouse. 

LEFT JOIN tng_families fams 
    ON fams.wife=p.personID OR fams.husband=p.personID AND fams.gedcom=p.gedcom
LEFT JOIN tng_people sp
    ON (IF(fams.wife=p.personID,sp.personID=fams.husband,sp.personID=fams.wife))
      AND sp.gedcom=p.gedcom

Logical Join to the Birth Note

Fortunately, the Notelinks table uses the built-in event Tag name and personID in the same way that the Citations table does, so we can just join to Notelinks where eventID='BIRT' and then to the related xnotes record, which contains the note itself. 

LEFT JOIN tng_notelinks n1 ON p.personid = n1.persfamID 
      AND n1.gedcom = p.gedcom AND n1.eventID = 'BIRT' 
LEFT JOIN tng_xnotes x1 ON x1.id = n1.xnoteid AND x1.gedcom = p.gedcom

Logical Join to the Burial or Death Note

Using the same Join construct as the Birth note, we can use the Tag that we found in the citation above to link to the Burial or Death Note. 

LEFT JOIN tng_notelinks n2 ON p.personid = n2.persfamID AND n2.gedcom = p.gedcom 
      AND n2.eventID = q1.tag 
LEFT JOIN tng_xnotes x2 ON x2.id = n2.xnoteid AND x2.gedcom = p.gedcom

Query for phpMyAdmin

This query is suitable for running in phpMyAdmin. See below for the query that will run in the TNG Reporting tool. The SELECT clause comments in this query would confuse the TNG Reporting Tool. 

SELECT p.personID, p.birthdatetr, CONCAT(p.firstname,' ',p.lastname,' ',p.suffix) AS Name,
    /* Parents' names may come from linked database records or from Birth notes */
    IF (d.lastname<>'',CONCAT(d.lastname,' & ',m.lastname),
       IF (LOCATE('Parents:',x1.note) , MID(x1.note, Locate('Parents:',x1.note), 20),
         IF (LOCATE('Son of ',x1.note), MID(x1.note, Locate('Son of ',x1.note), 20),
           IF (LOCATE('Dau of ',x1.note), MID(x1.note, Locate('Dau of ',x1.note), 20), '')))) AS Parents,
    /* Spouse name & marriage date. Note that multiple spouses will generate multiple rows in the report */
    CONCAT(sp.firstname,' ',sp.lastname) AS Spouse, fams.marrdate,
    /* The Built-in Event Tag that we are focusing on */
    q1.tag, 
    /* The Place associated with that Built-in Event */
    IF(q1.tag='BURI', p.burialplace, IF(q1.tag='DEAT',p.deathplace, p.birthplace)) AS Place,
    /* Show at most 80 characters of the EventNote */
    IF(LENGTH(x2.note)>80, CONCAT(LEFT(x2.note,80),'&hellip;'), x2.note) AS EventNote,
    /* Show at most 50 characters of the Citation Text */
    IF(LENGTH(citetext)>50, CONCAT(LEFT(citetext,50),'&hellip;'), citetext) AS CiteTxt,
    /* Show at most 20 characters of the Citation Page field (without an ellipsis) */
    LEFT(page,20) AS CitePage, 
    /* Show at most 10 characters of the Source Title, which is pretty meaningless, anyway */
    LEFT(REPLACE(s.title, 'Web:', ''), 10) AS STitle,
	/* Try to show a reason that this record was placed in the report */
    IF(page NOT LIKE "grid%" OR LENGTH(page)<16,"page",
      IF(x2.note NOT LIKE "%GRid=%" OR length(x2.note)<16, "note",
        IF(citetext NOT LIKE "%birth:%","xBir",
          IF(citetext NOT LIKE "%death:%","xDea",
            IF(x2.note LIKE "%    %","Spc","?"))))) AS Why

/* Start with Sources whose titles contain 'grave' and Join them with a Citations
query that assures that we get only one Citation record for each Person */
FROM tng_sources s
INNER JOIN 
    /* Contrive a sort order for the Event tags in the Citation records so that we'll get */
    /* the Burial event if it has a FindAGrave citation. If there is no Burial event, we'll */
    /* get the Death event, and then other event tags in arbitrary alpha order */
   (SELECT sourceID, gedcom, persfamID, MIN(eventID) AS tag, page, citetext 
     FROM tng_citations c
     WHERE eventID = 'BURI' OR eventID = 'DEAT'
     GROUP BY sourceID,gedcom) AS q1 ON q1.sourceID=s.sourceID AND q1.gedcom=s.gedcom
/* Use the Branchlinks table to speed up the query and restrict the number of matches */
/* Substitute any BranchID here, or just remove this line if you want to use a branch */
INNER JOIN tng_branchlinks b ON b.persfamID=q1.persfamID AND b.gedcom=q1.gedcom AND b.branch='closeanc'
/* If you delete the INNER JOIN to Branchlinks, then replace 'b.' with 'q1' in the JOIN to People */
INNER JOIN tng_people p ON p.personID=b.persfamID AND p.gedcom=b.gedcom
/*Join to the Children and Families tables to get the parents' marriage, and then People
/* records aliased as d (for Dad) and m (for Mom) */
LEFT JOIN tng_children ch ON ch.personID=p.personID AND ch.gedcom=p.gedcom
LEFT JOIN tng_families famc ON famc.familyID=ch.familyID AND famc.gedcom=ch.gedcom
LEFT JOIN tng_people d ON d.personID=famc.husband AND d.gedcom=famc.gedcom
LEFT JOIN tng_people m ON m.personID=famc.wife AND m.gedcom=famc.gedcom
/* Join to the Families records in which this person is a spouse and then to the People */
/* records for the spouses.  Note that these Joins will create multiple rows in the results */
/* when there are multiple spouses. */
LEFT JOIN tng_families fams 
    ON fams.wife=p.personID OR fams.husband=p.personID AND fams.gedcom=p.gedcom
LEFT JOIN tng_people sp
    ON (IF(fams.wife=p.personID,sp.personID=fams.husband,sp.personID=fams.wife))
      AND sp.gedcom=p.gedcom
/* Table aliases n1 and x1 get us the Birth note, which may contain parents' names */
LEFT JOIN tng_notelinks n1 ON p.personid = n1.persfamID 
      AND n1.gedcom = p.gedcom AND n1.eventID = 'BIRT' 
LEFT JOIN tng_xnotes x1 ON x1.id = n1.xnoteid AND x1.gedcom = p.gedcom
/* Table aliases n2 and x2 get us the note associated with the Event that we found */
LEFT JOIN tng_notelinks n2 ON p.personid = n2.persfamID AND n2.gedcom = p.gedcom 
      AND n2.eventID = q1.tag,
LEFT JOIN tng_xnotes x2 ON x2.id = n2.xnoteid AND x2.gedcom = p.gedcom

/* I'm focusing on a specific tree, and Sources whose title contains "grave" */
WHERE s.gedcom='rr' AND s.title LIKE '%grave%' AND
    /* Select records where the event note doesn't have a GRid, or is too short */
	/* to have any kind of description of the FindAGrave page */
    (x2.note IS NULL OR x2.note NOT LIKE "%GRid=%" 
     OR LENGTH(x2.note)<16 OR x2.note LIKE "%    %"
     /* Select records where Citation Text doesn't have the Birth: or Death: values */
	 /* that should have been copied from the FindAGrave page */
     OR citetext NOT LIKE "%Birth:%" OR citetext NOT LIKE "%Death:%" 
	 /* SELECT records where the Citation Page does not have a GRid or is too short to have a name */
     OR page NOT LIKE "GRid%" OR LENGTH(page)<16
    )
ORDER BY p.lastname, p.firstname, p.suffix, p.birthdate, p.personID

Query for the TNG Reporting Tool

This differs from the query above in that

  1. This query converts end-of-line marks in the CitationText and Event Note fields to
    tags, and
  2. Comments in the SELECT clause have been removed because the TNG Reports tool gets confused by them.

In the TNG Reporting Tool, the PersonID column in this query is automatically hyperlinked, but since I don't display a Gedcom column, the Gedcom querystringvalue is missing from the hyperlink. For the PersonID query to work, you must either

  • Add a column containing just s.gedcom,
  • Have only one tree in your database, or
  • Have a default tree defined in your database.
SELECT p.personID, p.birthdatetr, CONCAT(p.firstname,' ',p.lastname,' ',p.suffix) AS Name,
     IF (d.lastname<>'',CONCAT(d.lastname,' & ',m.lastname),
       IF (LOCATE('Parents:',x1.note) , MID(x1.note, Locate('Parents:',x1.note)+9, 20),
         IF (LOCATE('Son of ',x1.note), MID(x1.note, Locate('Son of ',x1.note)+7, 20),
           IF (LOCATE('Dau of ',x1.note), MID(x1.note, Locate('Dau of ',x1.note)+7, 20), '')))) AS Parents,
    CONCAT(sp.firstname,' ',sp.lastname) AS Spouse, fams.marrdate,
    q1.tag, 
    IF(q1.tag='BURI', p.burialplace, IF(q1.tag='DEAT',p.deathplace ,p.birthplace)) AS Place,
    REPLACE( REPLACE( IF( LENGTH(x2.note)>80, CONCAT(LEFT(x2.note,80),'&hellip;'), x2.note), '\n','<br>'), '\r','<br>') AS EventNote,
    REPLACE( REPLACE( IF(LENGTH(citetext)>50, REPLACE(CONCAT(LEFT(citetext,50),'&hellip;'), citetext), '\n','<br>'), '\r','<br>') AS CiteTxt,
    LEFT(page,20) AS CitePage, 
    LEFT(REPLACE(s.title, 'Web:', ''), 10) AS STitle,
    IF(page NOT LIKE "grid%" OR LENGTH(page)<16,"page",
      IF(x2.note NOT LIKE "%GRid=%" OR length(x2.note)<16, "note",
        IF(citetext NOT LIKE "%birth:%","xBir",
          IF(citetext NOT LIKE "%death:%","xDea",
            IF(x2.note LIKE "%    %","Spc","?"))))) AS Why

FROM tng_sources s
INNER JOIN 
    /* Contrive a sort order for the Event tags in the Citation records so that we'll get */
    /* the Burial event if it has a FindAGrave citation. If there is no Burial event, we'll */
    /* get the Death event, and then other event tags in arbitrary alpha order */
   (SELECT sourceID, gedcom, persfamID, MIN(eventID) AS tag, page, citetext 
     FROM tng_citations c
     WHERE eventID = 'BURI' OR eventID = 'DEAT'
     GROUP BY sourceID,gedcom) AS q1 ON q1.sourceID=s.sourceID AND q1.gedcom=s.gedcom
/* Use the Branchlinks table to speed up the query and restrict the number of matches */
/* Substitute any BranchID here, or just remove this line if you want to use a branch */
INNER JOIN tng_branchlinks b ON b.persfamID=q1.persfamID AND b.gedcom=q1.gedcom AND b.branch='closeanc'
/* If you delete the INNER JOIN to Branchlinks, then replace 'b.' with 'q1' in the JOIN to People */
INNER JOIN tng_people p ON p.personID=b.persfamID AND p.gedcom=b.gedcom
/*Join to the Children and Families tables to get the parents' marriage, and then People
/* records aliased as d (for Dad) and m (for Mom) */
LEFT JOIN tng_children ch ON ch.personID=p.personID AND ch.gedcom=p.gedcom
LEFT JOIN tng_families famc ON famc.familyID=ch.familyID AND famc.gedcom=ch.gedcom
LEFT JOIN tng_people d ON d.personID=famc.husband AND d.gedcom=famc.gedcom
LEFT JOIN tng_people m ON m.personID=famc.wife AND m.gedcom=famc.gedcom
/* Join to the Families records in which this person is a spouse and then to the People */
/* records for the spouses.  Note that these Joins will create multiple rows in the results */
/* when there are multiple spouses. */
LEFT JOIN tng_families fams 
    ON fams.wife=p.personID OR fams.husband=p.personID AND fams.gedcom=p.gedcom
LEFT JOIN tng_people sp
    ON (IF(fams.wife=p.personID,sp.personID=fams.husband,sp.personID=fams.wife))
      AND sp.gedcom=p.gedcom
/* Table aliases n1 and x1 get us the Birth note, which may contain parents' names */
LEFT JOIN tng_notelinks n1 ON p.personid = n1.persfamID 
      AND n1.gedcom = p.gedcom AND n1.eventID = 'BIRT' 
LEFT JOIN tng_xnotes x1 ON x1.id = n1.xnoteid AND x1.gedcom = p.gedcom
/* Table aliases n2 and x2 get us the note associated with the Event that we found */
LEFT JOIN tng_notelinks n2 ON p.personid = n2.persfamID AND n2.gedcom = p.gedcom 
      AND n2.eventID = q1.tag 
LEFT JOIN tng_xnotes x2 ON x2.id = n2.xnoteid AND x2.gedcom = p.gedcom

/* I'm focusing on a specific tree, and Sources whose title contains "grave" */
WHERE s.gedcom='rr' AND s.title LIKE '%grave%' AND
    /* Select records where the event note doesn't have a GRid, or is too short */
	/* to have any kind of description of the FindAGrave page */
    (x2.note IS NULL OR x2.note NOT LIKE "%GRid=%" 
     OR LENGTH(x2.note)<16 OR x2.note LIKE "%    %"
     /* Select records where Citation Text doesn't have the Birth: or Death: values */
	 /* that should have been copied from the FindAGrave page */
     OR citetext NOT LIKE "%Birth:%" OR citetext NOT LIKE "%Death:%" 
	 /* SELECT records where the Citation Page does not have a GRid or is too short to have a name */
     OR page NOT LIKE "GRid%" OR LENGTH(page)<16
    )
ORDER BY p.lastname, p.firstname, p.suffix, p.birthdate, p.personID

TNG Reporting Tool Query Without the Birth Note

This query would be most appropriate if you do not use the Birth note to identify the parents when you choose not to create database records for the parents. 

SELECT p.personID, p.birthdatetr, CONCAT(p.firstname,' ',p.lastname,' ',p.suffix) AS Name,
    CONCAT(d.lastname,' & ',m.lastname) AS Parents,
    CONCAT(sp.firstname,' ',sp.lastname) AS Spouse, fams.marrdate,
    q1.tag, 
    IF(q1.tag='BURI', p.burialplace, IF(q1.tag='DEAT',p.deathplace ,p.birthplace)) AS Place,
    REPLACE( REPLACE( IF( LENGTH(x2.note)>80, CONCAT(LEFT(x2.note,80),'&hellip;'), x2.note), '\n','<br>'), '\r','<br>') AS EventNote,
    REPLACE( REPLACE( IF(LENGTH(citetext)>50, REPLACE(CONCAT(LEFT(citetext,50),'&hellip;'), citetext), '\n','<br>'), '\r','<br>') AS CiteTxt,
    LEFT(page,20) AS CitePage, 
    LEFT(REPLACE(s.title, 'Web:', ''), 10) AS STitle,
    IF(page NOT LIKE "grid%" OR LENGTH(page)<16,"page",
      IF(x2.note NOT LIKE "%GRid=%" OR length(x2.note)<16, "note",
        IF(citetext NOT LIKE "%birth:%","xBir",
          IF(citetext NOT LIKE "%death:%","xDea",
            IF(x2.note LIKE "%    %","Spc","?"))))) AS Why

FROM tng_sources s
INNER JOIN 
    /* Contrive a sort order for the Event tags in the Citation records so that we'll get */
    /* the Burial event if it has a FindAGrave citation. If there is no Burial event, we'll */
    /* get the Death event, and then other event tags in arbitrary alpha order */
   (SELECT sourceID, gedcom, persfamID, MIN(eventID) AS tag, page, citetext 
     FROM tng_citations c
     WHERE eventID = 'BURI' OR eventID = 'DEAT'
     GROUP BY sourceID,gedcom) AS q1 ON q1.sourceID=s.sourceID AND q1.gedcom=s.gedcom
/* Use the Branchlinks table to speed up the query and restrict the number of matches */
/* Substitute any BranchID here, or just remove this line if you want to use a branch */
INNER JOIN tng_branchlinks b ON b.persfamID=q1.persfamID AND b.gedcom=q1.gedcom AND b.branch='closeanc'
/* If you delete the INNER JOIN to Branchlinks, then replace 'b.' with 'q1' in the JOIN to People */
INNER JOIN tng_people p ON p.personID=b.persfamID AND p.gedcom=b.gedcom
/*Join to the Children and Families tables to get the parents' marriage, and then People
/* records aliased as d (for Dad) and m (for Mom) */
LEFT JOIN tng_children ch ON ch.personID=p.personID AND ch.gedcom=p.gedcom
LEFT JOIN tng_families famc ON famc.familyID=ch.familyID AND famc.gedcom=ch.gedcom
LEFT JOIN tng_people d ON d.personID=famc.husband AND d.gedcom=famc.gedcom
LEFT JOIN tng_people m ON m.personID=famc.wife AND m.gedcom=famc.gedcom
/* Join to the Families records in which this person is a spouse and then to the People */
/* records for the spouses.  Note that these Joins will create multiple rows in the results */
/* when there are multiple spouses. */
LEFT JOIN tng_families fams 
    ON fams.wife=p.personID OR fams.husband=p.personID AND fams.gedcom=p.gedcom
LEFT JOIN tng_people sp
    ON (IF(fams.wife=p.personID,sp.personID=fams.husband,sp.personID=fams.wife))
      AND sp.gedcom=p.gedcom
/* Table aliases n1 and x1 get us the Birth note, which may contain parents' names */
LEFT JOIN tng_notelinks n1 ON p.personid = n1.persfamID 
      AND n1.gedcom = p.gedcom AND n1.eventID = 'BIRT' 
LEFT JOIN tng_xnotes x1 ON x1.id = n1.xnoteid AND x1.gedcom = p.gedcom
/* Table aliases n2 and x2 get us the note associated with the Event that we found */
LEFT JOIN tng_notelinks n2 ON p.personid = n2.persfamID AND n2.gedcom = p.gedcom 
      AND n2.eventID = q1.tag 
LEFT JOIN tng_xnotes x2 ON x2.id = n2.xnoteid AND x2.gedcom = p.gedcom

/* I'm focusing on a specific tree, and Sources whose title contains "grave" */
WHERE s.gedcom='rr' AND s.title LIKE '%grave%' AND
    /* Select records where the event note doesn't have a GRid, or is too short */
	/* to have any kind of description of the FindAGrave page */
    (x2.note IS NULL OR x2.note NOT LIKE "%GRid=%" 
     OR LENGTH(x2.note)<16 OR x2.note LIKE "%    %"
     /* Select records where Citation Text doesn't have the Birth: or Death: values */
	 /* that should have been copied from the FindAGrave page */
     OR citetext NOT LIKE "%Birth:%" OR citetext NOT LIKE "%Death:%" 
	 /* SELECT records where the Citation Page does not have a GRid or is too short to have a name */
     OR page NOT LIKE "GRid%" OR LENGTH(page)<16
    )
ORDER BY p.lastname, p.firstname, p.suffix, p.birthdate, p.personID
Retrieved from "https://tng.lythgoes.net/wiki/index.php?title=Report:_FindaGrave_Citation_Worklist&oldid=82963"