Jump to content
TNG Community
wlie

Report with missing individuals

Recommended Posts

wlie

Help is sought for a report.

I'm not very good at SQL so this report I "stole" from another side:

I have 12322 individuals in my database but the report only shows 8381.

The report does not appear to show individuals where mother or father are missing but I want all individuals to be included. How to???

My report:

SELECT p.personID, p.firstname, p.lastname, p.birthdate, p.birthplace, p.altbirthdate, p.altbirthplace, p.deathdate, p.deathplace, p.burialdate, p.burialplace, father.personID AS FarID , father.firstname AS Father first name, father.lastname AS Father first name, mother.personID AS MotherID, mother.firstname AS Mother first name, mother.lastname AS Mother first name, marrdate as ParentVielse

FROM tng_children AS c LEFT JOIN tng_families AS f ON (c.familyID = f.familyID AND c.gedcom = f.gedcom) LEFT JOIN tng_people AS p ON (c.personID = p.personID AND c.gedcom = p.gedcom) LEFT JOIN tng_people AS mother ON (f.wife = mother.personID AND f.gedcom = mother.gedcom) LEFT JOIN tng_people AS father ON (f.husband = father.personID AND f.gedcom = father.gedcom)

ORDER BY p.personID

Regards William

Share this post


Link to post
Share on other sites
Robin Richmond

Good query! Start with people and then LEFT JOIN to children, and then to Families, etc.

Share this post


Link to post
Share on other sites
Scott_Pryer

William,

Aside from the report you found, what exactly do you want on a report?

Share this post


Link to post
Share on other sites
wlie

Scott_Pryer thank you for your response.

My need is a list of all people in my database including all details.
and in addition (very important) similar details about each person's parents.

Regards William

Share this post


Link to post
Share on other sites
Scott_Pryer

William,

Just getting to your report.  Try this SQL query. good luck!

SELECT p.personID, p.firstname, p.lastname, p.birthdate, p.birthplace, p.altbirthdate, p.altbirthplace, p.deathdate, p.deathplace, p.burialdate, p.burialplace, 
c.familyID,fph.husband as fatherID ,  fpw.wife AS MotherID, fph.firstnameh, fph.lastnameh, fpw.firstnamew, fpw.lastnamew, fph.marrdate,fpw.marrdate FROM tng_people AS p LEFT JOIN tng_children AS c 
ON c.personID = p.personID AND c.gedcom = p.gedcom LEFT JOIN 
(select tng_families.familyID, husband, firstname as firstnameh,lastname as lastnameh, marrdate ,tng_people.gedcom from  tng_people,tng_families  where  tng_people.personID=husband  and tng_people.gedcom = tng_families.gedcom ) AS fph ON 
fph.familyID = c.familyID AND fph.gedcom = p.gedcom LEFT JOIN 
(select tng_families.familyID, wife, firstname as firstnamew,lastname as lastnamew,marrdate ,tng_people.gedcom from  tng_people,tng_families  where  tng_people.personID=wife  and tng_people.gedcom = tng_families.gedcom  ) AS fpw ON 
fpw.familyID = c.familyID AND fpw.gedcom = p.gedcom 

Share this post


Link to post
Share on other sites
Robin Richmond
On 12/11/2019 at 8:57 AM, Robin Richmond said:

Good query! Start with people and then LEFT JOIN to children, and then to Families, etc.

I've taken the liberty of placing the full name in one column to save space, and changing some labels for the same purpose.

Other than that, the only changes I made to your SELECT list were to add suffixes to the each full name.

SELECT p.personID, concat_ws(' ',p.firstname, p.lastname, p.suffix) AS fullname,
   p.birthdate, p.birthplace, p.altbirthdate, p.altbirthplace, p.deathdate, p.deathplace, p.burialdate, p.burialplace,
   father.personID AS DadID, concat_ws(' ', father.firstname, father.lastname, father.suffix) AS Dad,
   mother.personID AS MomID, concat_ws(' ', mother.firstname, mother.lastname, mother.suffix) AS Mother, marrdate as ParentVielse
FROM tng_people p
LEFT JOIN tng_children AS c ON (c.personID = p.personID AND c.gedcom = p.gedcom)
LEFT JOIN tng_families AS f ON (c.familyID = f.familyID AND c.gedcom = f.gedcom)
LEFT JOIN tng_people AS mother ON (f.wife = mother.personID AND f.gedcom = mother.gedcom)
LEFT JOIN tng_people AS father ON (f.husband = father.personID AND f.gedcom = father.gedcom)
ORDER BY p.personID

 

Share this post


Link to post
Share on other sites
wlie

Scott_Pryer

Many thanks for your help.
Everything works perfectly and all people are now included in the report.
I am very grateful for your help - thanks again.

If you come to Denmark I give you a cup of coffee or a beer.

greeting William

Share this post


Link to post
Share on other sites
wlie

Scott_Pryer
I forgot...
Your second edition where you have tried to gather first and last name comes out with an error.
This part "concat_ws ('', p.firstname, p.lastname, p.suffix) AS fullname" results in the field appearing blank.
The parent's name is perfect.

William

 

Share this post


Link to post
Share on other sites
Robin Richmond
5 hours ago, wlie said:

your second edition where you have tried to gather first and last name comes out with an error.
This part "concat_ws ('', p.firstname, p.lastname, p.suffix) AS fullname" results in the field appearing blank.
The parent's name is perfect.

It must have been a copy-and-paste error somewhere. When I look at the SQL code in the earlier message, I see 

 

15 hours ago, Robin Richmond said:

SELECT p.personID, concat_ws(' ',p.firstname,..

Here, the space between the two single-quotes is much less obvious than the the full SQL query, and in your snippet, it is gone altogether.

- Robin

p.s. I'm not sure what "WS" stands for in "concat_WS ", but, FWIW, that function takes the first argument (which I defined as a space) and places it between the other arguments.  The function "concat" is a more traditional concatenation function.  If you want your names to appear as "Last, First suffix", you could say

concat(p.last, ', ', p.first, ', ', p.suffix) as Fullname

where both quoted strings contains a comma and a space.

Share this post


Link to post
Share on other sites
wlie

Robin Richmond
I am speechless
In the second edition I have tried to make two spaces between the two single quotes, but get the same result that the field is blank.
I have also tried to remove both the spaces and the quotes completely and again with the same result, namely that the field is blank.
I don't get it, because the names of both father and mother appear as they should. This despite the arguments being similar.

I apologize if my English doesn't make this understandable.

William

Share this post


Link to post
Share on other sites
Scott_Pryer

The "concat_ws ('', p.firstname, p.lastname, p.suffix) AS fullname" column works when I run in in cpanel. So apparently something is different when running it in TNG_reports.

Hope this will still accomplish what was originally wanted.

Share this post


Link to post
Share on other sites
wlie

Scott_Pryer
You wrote: Hope this will still accomplish what was originally wanted.
Agree - your first input solved my problem 100%.
Thanks again 

William

Share this post


Link to post
Share on other sites
Robin Richmond

I realize that Scott's query is already working for you,  but to get that first fullname to work, you might try the concat operator:

concat(p.lastname, ' ', p.firstname, ' ', p.suffix)

 

18 hours ago, wlie said:

I apologize if my English doesn't make this understandable.

Actually, as seems to be true with most Scandinavians, your English was not distinguishable from that of a native English speaker.

 

On 12/13/2019 at 6:21 AM, wlie said:

If you come to Denmark I give you a cup of coffee or a beer.

I know that you said that to Scott :-), but still, I intend to go there and have a drink with you!

I almost made it to Denmark last year, and had hoped to meet up with a TNG user there. (Shoot, I can't remember who at the moment, but I have it recorded somewhere.) I was in Amsterdam scoping out the neighborhoods where early 17th-century ancestors lived, and headed off toward Norstrand, where ancestors were evidently driven away by the disastrous storm of 1634.  Well, of course, Norstand is now in Germany, but still, I intended to go on into Denmark.  I've always had a thing for Copenhagen; I've traveled a good bit, and Copenhagen is one of only two overseas destinations on my bucket list.

Anyway, I only made it as far as Bremen before realizing that I just didn't have enough time to get there and back.  But, I hope to get there before long, and will plan check in with you when I do.

-------------

- Robin

 

Share this post


Link to post
Share on other sites
Robin Richmond

BTW - Scott, I don't know if we had this discussion a few years ago, but as long as I've digressed into travels and destinations, I'll note that I largely grew up in Tulsa. I graduated from Memorial High School in, geez, 1971.  My folks moved away sometime near the turn of the century, and I've only been back once since then.  But it was a good place to be a kid.

I still frequently tell people about Tulsa's compulsively organized street system.  The only place I've lived that is at all comparable is Minneapolis, which has, like Tulsa, alphabetically sequenced street names and numbered Streets that intersect with numbered Avenues.  Minneapolis also has section of town where the streets are named after U.S. Presidents, in order of their terms as President. But it doesn't have two of Tulsa's street system features:

  • The first several Avenues on the west side of Main (the 0 block) are named for cities on the west side of the Mississippi River, and Avenues on the east side of Main are named for cities on the east side of the Mississippi, and
  • Almost all of the secondary arteries (busy, non, neighborhood streets) are on section lines (a grid with exactly one mile between those streets)

Also, I spoke of Copenhagen as a bucket list destination in my previous posting, and, as I think about it, getting back to Chandler Park has to be on my bucket list, too.  I know that I can't scramble and leap among the rock pillars in same way as I did as a teenager, but I like to imagine that I could still have some fun with them, or at least enjoy seeing them and reminiscing.

- Robin

p.s. I have to assume, of course, that William is following this digression with great interest, so I'll provide this link to what might be Google Map photos of the park. If the photos aren't immediately visible, just scroll down in the sidebar.   https://bit.ly/34lRLUj

p.p.s Scott, in case you haven't been there, Robber's Cave State Park (southeast of McAlester) has interesting rock formations, too, and some good hiking trails and cabins.  Or at least it did when my graduate school gang in Dallas used to go up there regularly.  Shoot - maybe I'll host a reunion!  If I do, I'll be sure to invite you. :-)

-------

End of digression, well, at least for the moment.

Share this post


Link to post
Share on other sites
wlie

Robin Richmond

Your suggestion "concat (p.lastname," ', p.firstname,' ', p.suffix) "did not help - still blank in this field.
But no need to get more involved with this. I solved my original problem.
Of course, my offer of a cup of coffee or beer applies to you too, but unfortunately I live 325 km from Copenhagen.

William

 

Share this post


Link to post
Share on other sites
Robin Richmond

I know that you solved your problem, and there is no need to dig further, but, FWIW,  a double-quote was paired with a single-quote in the snippet in your last post.

But much more importantly :-)

I keep forgetting how far east Copenhagen is.  Would you take the causeway (E20) or the ferry to Zealand and then Copenhagen?.  How's that ferry - and the one from Gothenberg?  I'm not asking about the Gothenberg ferry as a way to get there from Copenhagen, but  - not as a way to northern Denmark directly from  Copenhagen, but just as a way to get across the sea.

- Robin 

Share this post


Link to post
Share on other sites
wlie

Robin Richmond
To get to Copenhagen from my home I will take the car. Copenhagen is located on an island (Zealand) and you may also drive across another island (Fyn), but there are bridge connections between these islands.
OK - you can shorten the travel time by taking a ferry between Zealand and the city where I live. Ferry time about 75 minutes and the price of a car approx. $ 100 and you still have to drive about 200 km.

William

Share this post


Link to post
Share on other sites
Robin Richmond

I know that this isn't really needed, but I just had to keep looking at it until I understood why my syntax wasn't working.  Scott provided the key:

22 hours ago, Scott_Pryer said:

The "concat_ws ('', p.firstname, p.lastname, p.suffix) AS fullname" column works when I run in in cpanel. So apparently something is different when running it in TNG_reports.

The TNG reporting tool calculates "fullname" when it thinks it can, and doesn't like the query to have a column titled "fullname".  So when I changed "fullname" to "person", it all worked.

Also, though this would have been more helpful early in the discussion,  I'm going to make a shamless plug for two of my mods - 

  1. Admin Reports Search gives you more information on the search results page, including (in a popup) the SQL code of pure-SQL reports.
  2. Reports-Floating Editor makes the editing form much easier to use by
    1. Allowing the report-writer fields to float side-by-side, and
    2. Recognizing when a report consists of pure SQL, and giving you a much larger textarea field to work with.

- Robin

Share this post


Link to post
Share on other sites
wlie

Robin
Hurray, you solved your query problem, it's running perfectly now.
Even my own name is included in the report.
The change from "fulname" to "person" did the trick.

William

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×