wlie Posted December 10, 2019 Report Share Posted December 10, 2019 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 Quote Link to comment Share on other sites More sharing options...
Robin Richmond Posted December 11, 2019 Report Share Posted December 11, 2019 Good query! Start with people and then LEFT JOIN to children, and then to Families, etc. Quote Link to comment Share on other sites More sharing options...
Scott_Pryer Posted December 11, 2019 Report Share Posted December 11, 2019 William, Aside from the report you found, what exactly do you want on a report? Quote Link to comment Share on other sites More sharing options...
wlie Posted December 11, 2019 Author Report Share Posted December 11, 2019 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 Quote Link to comment Share on other sites More sharing options...
Scott_Pryer Posted December 13, 2019 Report Share Posted December 13, 2019 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 Quote Link to comment Share on other sites More sharing options...
Robin Richmond Posted December 13, 2019 Report Share Posted December 13, 2019 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 Quote Link to comment Share on other sites More sharing options...
wlie Posted December 13, 2019 Author Report Share Posted December 13, 2019 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 Quote Link to comment Share on other sites More sharing options...
wlie Posted December 13, 2019 Author Report Share Posted December 13, 2019 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 Quote Link to comment Share on other sites More sharing options...
Robin Richmond Posted December 13, 2019 Report Share Posted December 13, 2019 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. Quote Link to comment Share on other sites More sharing options...
wlie Posted December 13, 2019 Author Report Share Posted December 13, 2019 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 Quote Link to comment Share on other sites More sharing options...
Scott_Pryer Posted December 14, 2019 Report Share Posted December 14, 2019 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. Quote Link to comment Share on other sites More sharing options...
wlie Posted December 14, 2019 Author Report Share Posted December 14, 2019 Scott_Pryer You wrote: Hope this will still accomplish what was originally wanted. Agree - your first input solved my problem 100%. Thanks again William Quote Link to comment Share on other sites More sharing options...
Robin Richmond Posted December 14, 2019 Report Share Posted December 14, 2019 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 Quote Link to comment Share on other sites More sharing options...
Robin Richmond Posted December 14, 2019 Report Share Posted December 14, 2019 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. Quote Link to comment Share on other sites More sharing options...
wlie Posted December 14, 2019 Author Report Share Posted December 14, 2019 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 Quote Link to comment Share on other sites More sharing options...
Robin Richmond Posted December 14, 2019 Report Share Posted December 14, 2019 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 Quote Link to comment Share on other sites More sharing options...
wlie Posted December 15, 2019 Author Report Share Posted December 15, 2019 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 Quote Link to comment Share on other sites More sharing options...
Robin Richmond Posted December 15, 2019 Report Share Posted December 15, 2019 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 - Admin Reports Search gives you more information on the search results page, including (in a popup) the SQL code of pure-SQL reports. Reports-Floating Editor makes the editing form much easier to use by Allowing the report-writer fields to float side-by-side, and Recognizing when a report consists of pure SQL, and giving you a much larger textarea field to work with. - Robin Quote Link to comment Share on other sites More sharing options...
wlie Posted December 15, 2019 Author Report Share Posted December 15, 2019 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.