Maintenance - Places with no Events
Jump to navigation
Jump to search
Purpose
This article shows you how you can create an Admin Report that lists all places not linked to any event. Note that there is a new TNGv10 Places Subject to Deletion Mod that, at least to some degree, supersedes this article.
Creating the Report
- In Admin/Reports select the 'Add New' tab.
- Enter a Report Name and Description:
- Select the 'No' radio button against 'Active' (this ensures the report is viewable only by the system administrator).
- Copy the SQL code listed below and paste in the last text box (above the 'Save Report' button).
- BE SURE you replace ALL instances of 'mytree' with your tree ID (inside single quotes). OR, if you use are Pooling Places or have only one tree in your database, then you should be able to omit the tree and gedcom field references altogether.
- Save the Report.
SQL Code
Please note that this SQL code does not include these LDS fields
SELECT initplace FROM `tng_people
SELECT confplace FROM tng_people
SELECT endlplace FROM tng_people
SELECT sealplace FROM tng_children
SELECT sealplace FROM tng_families
SELECT id, gedcom, place, longitude, latitude, notes
FROM tng_places
WHERE gedcom = 'mytree'
AND place
IN (
SELECT pl.place
FROM tng_places AS pl
LEFT JOIN (
SELECT gedcom, birthplace AS place FROM tng_people
WHERE gedcom = 'mytree'
UNION SELECT gedcom, altbirthplace FROM tng_people
WHERE gedcom = 'mytree'
UNION SELECT gedcom, marrplace FROM tng_families
WHERE gedcom = 'mytree'
UNION SELECT gedcom, divplace FROM tng_families
WHERE gedcom = 'mytree'
UNION SELECT gedcom, deathplace FROM tng_people
WHERE gedcom = 'mytree'
UNION SELECT gedcom, burialplace FROM tng_people
WHERE gedcom = 'mytree'
UNION SELECT gedcom, eventplace FROM tng_events
WHERE gedcom = 'mytree'
UNION SELECT gedcom, personID FROM tng_medialinks
WHERE gedcom = 'mytree' AND linktype='L'
UNION SELECT 'mytree' as gedcom, place FROM tng_cemeteries
) AS p
USING ( gedcom, place )
WHERE pl.gedcom = 'mytree'
AND isnull( p.place )
)Note that the last two UNION clauses in the SQL statement above will find
- Places that are linked to media but are not used in the people, families, or events tables, and
- Places that are associated with a cemetery but not used in burial events.
So, you might wish to remove those two clauses from the SQL statement.
Viewing and Using the Report
After saving the report changes, click the 'Test' button. The resulting report should look like this:
Related pages
- Maintenance - Places - Zoom & Place Levels - Places with no Zoom or Place Level set
- Maintenance - Places with no maps - Places with no Latitude or Longitude set
- Places Subject to Deletion Mod
Acknowledgments
- Original SQL - Luke Weerts
- See TNG Forum: https://tng.community/index.php?/forums/topic/3093-not-used-placenames-report/&tab=comments#comment-14401 for a variety of 'tweaks' to the original SQL and that provided here.
