Reports Children with a different sounding surname than their father

From TNG_Wiki
Jump to navigation Jump to search

Since the spelling of surnames is sometimes slightly different I wanted to find a way to exclude them from the list "Children with a different surname than their father" So I decided to use the metaphoon. In my database however I got MORE results instead of less but different results. Enough to look at them carefully.


SELECT p.personID, p.lnprefix, p.lastname, p.firstname, p.birthdate, p.living, p.metaphone, p.gedcom, f.familyID, father.personID AS FatherNr, father.lnprefix, father.lastname AS Fatherlast_name, father.metaphone AS fathermetaphone, mother.personID AS MotherNr, mother.lastname AS Motherlast_name FROM tng_children AS ch LEFT JOIN tng_people AS p ON ( ch.personID = p.personID AND ch.gedcom = p.gedcom ) LEFT JOIN tng_families AS f ON ( ch.familyID = f.familyID AND ch.gedcom = f.gedcom ) LEFT JOIN tng_people AS father ON ( father.personID = f.husband AND father.gedcom = f.gedcom ) LEFT JOIN tng_people AS mother ON ( mother.personID = f.wife AND mother.gedcom = f.gedcom ) WHERE p.metaphone <> father.metaphone AND YEAR( p.birthdatetr ) > "1811" ORDER BY p.lastname, p.firstname, p.birthdatetr