Report: FindaGrave Citation Worklist

From TNG_Wiki
Jump to navigation Jump to search

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