Report: Wrong place names for PERSONS
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