Maintenance - Pooled Places with no Events

Purpose

This article shows you how you can create an Admin Report that lists all pooled places not linked to any event.

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).

File:Places-SQLcodebox4.jpg

Save the Report.

SQL Code

SELECT id, gedcom, concat('<a
href=admin_places.php?tree=',gedcom,'&searchstring=',REPLACE(place,'
','%20'),'&exactmatch=yes&findplace=1&newsearch=1>',place,'</a>') as place,
longitude, latitude, notes 
FROM tng_places 
WHERE place 
IN 
( SELECT pl.place FROM tng_places AS pl 
LEFT JOIN 
( 
SELECT birthplace AS place FROM tng_people 
UNION 
SELECT altbirthplace FROM tng_people 
UNION 
SELECT baptplace FROM tng_people
UNION 
SELECT deathplace FROM tng_people
UNION 
SELECT burialplace FROM tng_people 
UNION 
SELECT marrplace FROM tng_families
UNION 
SELECT divplace FROM tng_families 
UNION 
SELECT eventplace FROM tng_events 
) AS p 
USING ( place ) 
WHERE  isnull( p.place ) )

Viewing and Using the Report

After saving the report changes, click the 'Test' button to run the report from the Admin screen. The resulting report should look like this:

Click on the link in the returned list to open the Place in another window or tab, where you can then verify that no events are associated with the place.

The verify will confirm that no Events are linked to this Place, but since the report does not check for Media linked to the place, it is possible that a Media record is linked to the place as shown below. In which case you will need to relink that Media record before deleting the Place record.

You can then click the Delete icon to delete the place from your database.

Acknowledgments

Original SQL - Luke Weerts
Pooled Places SQL - Mark Wonson
See TNG Forum: http://www.tngforum.us/index.php?showtopic=3273 for a variety of 'tweaks' to the original SQL and that provided here.