From TNG_Wiki
Jump to navigation Jump to search
Reports icon.gif TNG Reports

This page needs expansion and has the following purposes:

  • Listing of generic reports constructed in such a way that any TNG novice could be successful at creating and using the reports query
  • Hopefully in time all TNG users will benefit by being exposed to SQL SELECT code and learn from the collective successes of other users.

Many, but not all, of these queries can be run in the TNG Reports tool. Some queries must be run in phpMyAdmin, or a similar program.

Sites with multiple trees

Not all of these queries are written to deal with multiple trees. You may need to modify some of them to take trees into account.  [Show How]

  • Frequently, all you need to do is specify the treeID in the query's WHERE clause. For example, you might need to change
WHERE lastname="SMITH" to WHERE lastname="Smith" AND gedcom="BobsTree"
  • You will almost surely want to make sure that linked records are in the same tree as each other. For instance, if you want to display the name of the wife in a Family records, you'll need to compare the two gedcom fields to each other like this:
... FROM tng_families f INNER JOIN tng_people w ON w.personID=f.wife AND w.gedcom=f.gedcom.
Significantly, many of the queries defined here already include a gedcom field comparison like this, even if they omit other considerations of having multiple tree. Omitting the gedcom field comparison can slow down a query considerably.
  • To display the treeID in its own columns, just start the SELECT clause with x.gedcom (where x represents the table alias of any of the tables in the query), e.g. change
SELECT personID, lnprefix, lastname, firstname, burialdate, burialdatetr FROM tng_people... to
SELECT gedcom, personID, lnprefix, lastname, firstname, burialdate, burialdatetr FROM tng_people...
  • To show results from all trees and sort the results by treeID, you may need nothing more than an ORDER BY clause such as
ORDER BY x.gedcom
[Hide Details]