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

• 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. The resulting report should look like this:

Related Links

Maintenance - Places with no maps

Maintenance - Places with no Events

Maintenance - Pooled Places with no Events

Maintenance - Places with identical geocodes

Maintenance - Places - Zoom & Place Levels

Places - Merging

Pooling Places

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.