Child to Parent Relationship Report

[dragonbreath]

I've been doing manual data entry for a large well-known family organization, and I need to run some data-verification reports.  The needed report does not exist in the Data Validation tab or TNG WIFI.  I know a little about SQL, but since the database structure has not been published, I don't know the tables and field names.  

Heres: my problem.  In my review of the records, I'm finding many realtionships between the children & the parents (Birth, Adopted, Stepchild, etc.) are blank.  Other than manually going through each record to find those blank fields, there must be a way to write a SQL query report to do that.  Does anyone have such a report, or can someone clue me in to the table name & data elements to use.  I know a little SQL, ut I'm a novice for sure.  Thanks, Carol

[GOGGS]

The way I use the system is to assume that if a value is NOT selected/entered, then it is a birth. But if you want to change them all...

First run a query (or create a report) to see what you might be changing:

SELECT * FROM tng_children WHERE frel = '' OR mrel = ''

If you're happy that all of the rows that are returned are the right ones to update, then do each of these to update all of the father's and mother's relationships:

UPDATE tng_children SET frel = 'birth' where frel = ''

UPDATE tng_children SET mrel = 'birth' where mrel = ''

Let me know what you think...

GOGGS

[Jeff Clenard]

I would like to use variations of the query suggested by GOGGS to assist with some data verification.

I know, for example, that there are only six (6) records in my database that reflect an "Adopted" Parent/Child relationship.

Using "Adopted" as my test case I created a report using the following query.

SELECT * FROM tng_children WHERE frel = 'Adopted' OR mrel = 'Adopted'

The results show six (6) rows, but none of the table columns are reflected in the output. How do I get the table columns to show up?

 

[GOGGS]

Hey Jeff - first, I must have assumed that you (or anyone) has access to a database tool like phpMyAdmin with their TNG site, but I realize that is not the case with some hosts. So to get a list of all the tables and field names for TNG, you could create a report to do that with this SQL:

SELECT c.table_name, c.column_name
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.table_name LIKE '%tng%'
ORDER BY c.table_name, c.column_name;

This will show all of the tables associated with TNG and their columns. So I also realize now that the TNG Report tool doesn't allow the asterisk wildcard like it does in phpMyAdmin (thanks). Without the "catalog" query above, you could do this SQL:

SELECT familyID, personID, gedcom, frel, mrel 
FROM tng_children
WHERE mrel = 'adopted' OR frel = 'adopted'

If you wanted names, parents, other info, you would need a query that joins this table to others. Feel free to reply for more help if that's the case...

 

GOGGS

[Jeff Clenard]

Thank you, GOGGS, for clarifying the code to use. Both SQL statements are helpful.

Your assumption was correct. I do have access to phpMyAdmin, but did not think of using it. I am currently running TNG locally using a MAMP server on an M4 Mac Mini desiring to complete a lot of cleanup before publishing a public site which I hope to do sometime this summer.

Your offer to share a query on how to join this table to others to show names, parents, etc is most welcome.

Jeff

 

[GOGGS]

Here it is:

SELECT familyID, c.personID AS personID, p.firstname AS first, p.lastname AS last, c.gedcom, frel, mrel 
FROM tng_children c INNer JOIN tng_people p ON c.gedcom = p.gedcom AND c.personID = p.personID 
WHERE mrel = 'adopted' OR frel = 'adopted';

 

GOGGS

[Jeff Clenard]

Thank you GOGGS. Just what I needed.

Jeff