Help Creating Direct SQL Select Statement for Report

[jhculbert]

I am conducting the Culbert One Name Study using TNG.  I would like to create 2 reports, one that identifies all instances for individuals as fathers where his Culbert surname differs from any child's surname and their relationship is a birth, and another for all instances for individuals as children where their Culbert surname differs from their father's surname and their relationship is a birth.  I am obviously NOT interested in adoption, foster, or stepchild relationships in these reports.  The options available under TNG Admin. on the "Reports >> Add New Report" page do not allow me to create these reports, and I know nothing about creating my own SQL statements.

For those genealogists interested, this situation occurs when children change their their surname spelling, or adopt a different surname, and it is not uncommon to be found in surname studies such as mine.  I want to be able to easily find those instances in my database, which now consists of almost 28,000 individuals.

Thank you in advance for any help with this.

[jayat1familytree]

Here is an existing report from the many of Henny Savenije's reports on the TNG Wiki

This looks for children with different surnames than both parents.

I do not see a reason why you need two reports, because the data comparison is in the same table

so a child with a different surname than the father will always also always be the father with a different surname than the child.

 

SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, father.lastname AS Father_Last_name, mother.lastname AS Mother_Last_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.lastname<>father.lastname AND p.lastname<>mother.lastname ORDER BY p.lastname, p.firstname, p.birthdatetr;

Hope that helps

 

 

 

 

[jhculbert]

Hello, and thank you for your reply! 

As I said in my initial post, I am studying the Culbert surname, so I REALLY DO need to compare Culbert fathers to variant children surnames, and also Culbert children to variant father surnames.  Thus, I need REALLY DO need two reports. 

Since I do not understand SQL coding, I do not know how to use the language you quoted - modified to fit my particular needs.

I will also comment that in this code there doesn't seem to be any distinction between the different kinds of family relationships, whereas I only want to find BIRTH relationships.

Can you help further by making modifications to that code so that this operates as I require?

[jayat1familytree]

I hope someone else will know how to better help you with the SQL code.

Still, regarding the two reports, I just do not understand.

 

There is one database table that links fathers to children and children to fathers...

If they are linked it will always be both ways.......so the report of children and their fathers surnames will always be exactly the same as the fathers with their childrens surnames.

Unless you are storing your relationships differently somehow.