In the Climb Our Family Tree section of this website, our family tree is stored in a database, MySQL if you must know. That endows The Next Generation of Genealogy Sitebuilding (TNG) software, integrated into this website, with superpowers—queries! Queries allow us to search the family tree for specific information that would be near impossible to find otherwise. TNG uses these queries to display reports.
We’ve provided quite a few pre-built reports at Climb Our Family Tree > Info > Reports. With these reports, you can quickly find all the immigrants, twins, or war veterans in the family tree, and much more.
Hint, hint … we have provided several reports that you can use if you are interested in taking on a project to research our family tree or correct obvious mistakes in its information. These reports are the ones whose names start with Help Correct … or Help Research ….
So what does a MySQL query look like? The Twins, Triplets, Etc. report runs this MySQL query:
SELECT c.familyID, p.personID, p.lastname, p.firstname, p.birthdate, p.birthplace, p.living, COUNT( c.familyID ) AS Number, p.gedcom
FROM tng_children AS c
INNER JOIN tng_people AS p ON p.personID = c.personID
INNER JOIN tng_children AS c2 ON c2.familyID = c.familyID
INNER JOIN tng_people AS p2 ON p2.personID = c2.personID
WHERE (
p2.birthdatetr = p.birthdatetr
OR p2.birthdatetr = DATE_ADD( p.birthdatetr, INTERVAL 1
DAY )
OR p2.birthdatetr = DATE_SUB( p.birthdatetr, INTERVAL 1
DAY )
)
AND YEAR( p.birthdatetr ) <>0
AND MONTH( p.birthdatetr ) <>0
AND DAYOFMONTH( p.birthdatetr ) <>0
GROUP BY c.familyID, p.personID, p.birthdatetr
HAVING COUNT( c2.familyID ) >=2
ORDER BY p.lastname, c.familyID, p.birthdatetr
That basically just says to list all the siblings in the tree that share the same birthday with one of their siblings, with some extra checking thrown in to ensure the birthdate is valid, and the list is then ordered by last name, then family ID, and then birthdate so the siblings are grouped together in the list.
We also have many other reports that are used by the family tree administrator to manage the family tree (e.g. finding typos and invalid characters in names, dates, and places; ensuring living family members are indeed listed as Living, ensuring recently deceased family members are listed as Private, and countless other tedious tasks). Consider yourself spared.
Many of these reports were generated by the TNG Community and can be found here.
If you have an idea for a report, let us know, and we’ll see if we can find or create the needed query.