Report: Wrong place names for PERSONS

From TNG_Wiki
Revision as of 23:18, 26 June 2011 by Henny (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Description: Places to persons, which are NOT in the places table (check for data plausibility)


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


SELECT personID, lastname, firstname, "Place of birth" AS Kind_of_place, birthplace 
AS place_detail, living, p.gedcom FROM tng_people AS p LEFT JOIN tng_places 
AS pl ON (p.birthplace=pl.place AND p.gedcom=pl.gedcom) WHERE ISNULL(place) 
AND birthplace<>"" UNION SELECT personID, lastname, firstname, "Place of birth" 
AS Kind_of_place, altbirthplace AS place_detail, living, p.gedcom FROM tng_people 
AS p LEFT JOIN tng_places AS pl ON (p.altbirthplace=pl.place AND p.gedcom=pl.gedcom) 
WHERE ISNULL(place) AND altbirthplace<>"" UNION SELECT personID, lastname, firstname, "Place of death" 
AS Kind_of_place, deathplace AS place_detail, living, p.gedcom FROM tng_people 
AS p LEFT JOIN tng_places AS pl ON (p.deathplace=pl.place AND p.gedcom=pl.gedcom) 
WHERE ISNULL(place) AND deathplace<>"" UNION SELECT personID, lastname, firstname, "Place of burial" 
AS Kind_of_place, burialplace AS place_detail, living, p.gedcom FROM tng_people 
AS p LEFT JOIN tng_places AS pl ON (p.burialplace=pl.place AND p.gedcom=pl.gedcom) 
WHERE ISNULL(place) AND burialplace<>"" UNION SELECT personID, lastname, firstname, "Place of event" 
AS Kind_of_place, eventplace AS place_detail, living, p.gedcom FROM tng_events 
AS e LEFT JOIN tng_places AS pl ON (e.eventplace=pl.place AND e.gedcom=pl.gedcom) LEFT JOIN tng_people 
AS p ON (e.persfamID=p.personID AND e.gedcom=p.gedcom) WHERE ISNULL(place) AND eventplace<>"" 
ORDER BY lastname, firstname