Report: Places with no people attached

From TNG_Wiki
Jump to navigation Jump to search

Description: Places with no people attached

Copy and paste the text between the lines into the reports section below "OR Leave Display, Criteria and Sort fields blank and enter direct SQL SELECT statement here:" and give it the title you think is appropriate

Note that in the following SQL query you need to replace the gedcom = 'treeID' with the treeID for your web site.

The original report was updated by Roger Moffat to also check the tng_families table divplace and tng_people baptplace fields, as shown below:


SELECT id, gedcom, place, longitude, latitude, notes
FROM tng_places
WHERE gedcom = 'treeID'
AND place
IN (

SELECT pl.place
FROM tng_places AS pl
LEFT JOIN (

SELECT gedcom, birthplace AS place
FROM `tng_people` 
WHERE gedcom = 'treeID'
UNION SELECT gedcom, altbirthplace
FROM `tng_people` 
WHERE gedcom = 'treeID'
UNION SELECT gedcom, baptplace
FROM `tng_people` 
WHERE gedcom = 'treeID'
UNION SELECT gedcom, deathplace
FROM `tng_people` 
WHERE gedcom = 'treeID'
UNION SELECT gedcom, burialplace
FROM `tng_people` 
WHERE gedcom = 'treeID'
UNION SELECT gedcom, eventplace
FROM tng_events
WHERE gedcom = 'treeID'
UNION SELECT gedcom, marrplace
FROM `tng_families` 
WHERE gedcom = 'treeID'
UNION SELECT gedcom, divplace
FROM `tng_families` 
WHERE gedcom = 'treeID'
) AS p
USING ( gedcom, place ) 
WHERE pl.gedcom = 'treeID'
AND ISNULL( p.place )
)