Rob Roy Posted April 11, 2019 Report Share Posted April 11, 2019 The TNG SQL generator fails, and my knowledge of SQL is minimal. I have created a custom event. I can easily produce a report showing all people with this event. What I want is a report showing all people without this event. Let us say the event is tng_test. I think I can get by with just the where clause, but any help would be appreciated. Quote Link to comment Share on other sites More sharing options...
JPB Posted April 11, 2019 Report Share Posted April 11, 2019 Rob, Why do you not use the "Search People" page? At the bottom of this page you can open "Other Events" (Click to hide) and select "does not exist" for the desired event (see the picture). Jürgen Quote Link to comment Share on other sites More sharing options...
Rob Roy Posted April 11, 2019 Author Report Share Posted April 11, 2019 Nice feature but doesn't do what I need. I entered as follows: I get the two records which have the event Full Review, but not those which that event has not been created. I need a list of records for which the event has not been created. Thank you though, I hadn't explored the advanced search feature. Quote Link to comment Share on other sites More sharing options...
Ken Roy Posted April 11, 2019 Report Share Posted April 11, 2019 Have you checked the TNG Wiki Reports category ? Quote Link to comment Share on other sites More sharing options...
Rob Roy Posted April 11, 2019 Author Report Share Posted April 11, 2019 Yes, I searched for a similar report but found none. What I need is a WHERE statement that is akin to WHERE tag does not exist. Rob Roy Quote Link to comment Share on other sites More sharing options...
Brett Posted April 11, 2019 Report Share Posted April 11, 2019 Rob Are you able to provide the SQL you have used please? Quote Link to comment Share on other sites More sharing options...
Rob Roy Posted April 11, 2019 Author Report Share Posted April 11, 2019 SELECT roytng_people.lastname, firstname,birthdate, roytng_people.personID FROM (roytng_people ) WHERE NOT EXISTS roytng_eventtypes.tag='REVIEW' ORDER BY lastname,firstname The Select and the Order By clauses work. It is the WHERE that I need. The Event is "Review" and I want to list all records where that event does not occur. Hope this helps. Rob Roy Quote Link to comment Share on other sites More sharing options...
Mark Posted April 12, 2019 Report Share Posted April 12, 2019 Try SELECT p.personid, p.firstname, p.lastname FROM tng_people p WHERE p.personid NOT IN ( SELECT p.personID FROM tng_people p INNER JOIN tng_events e ON p.personID = e.persfamID AND p.gedcom = e.gedcom INNER JOIN tng_eventtypes et ON e.eventtypeID = et.eventtypeID WHERE et.tag = 'REVIEW' ) ORDER BY p.lastname, p.firstname Regards Mark Quote Link to comment Share on other sites More sharing options...
Rob Roy Posted April 12, 2019 Author Report Share Posted April 12, 2019 It failed. Since this is the second instance of TNG (both licensed) I had to add "roy" in front of "tng" Here is how it looks now: SELECT p.personid, p.firstname, p.lastname FROM roytng_people p WHERE p.personid NOT IN ( SELECT p.personID FROM roytng_people p INNER JOIN roytng_events e ON p.personID = e.persfamID AND p.gedcom = e.gedcom INNER JOIN roytng_eventtypes et ON e.eventtypeID = et.eventtypeID WHERE et.tag = 'REVIEW' ) ORDER BY p.lastname, p.firstname I ran it twice with the same result. Here is the result: SQL: SELECT p.personid, p.firstname, p.lastname FROM roytng_people p WHERE p.personid NOT IN ( SELECT p.personID FROM roytng_people p INNER JOIN roytng_events e ON p.personID = e.persfamID AND p.gedcom = e.gedcom INNER JOIN roytng_eventtypes et ON e.eventtypeID = et.eventtypeID WHERE et.tag = 'REVIEW' ) ORDER BY p.lastname, p.firstname Warning: mysqli_query(): MySQL server has gone away in /homepages/40/d127839664/htdocs/roygen/tngconnect.php on line 157Warning: mysqli_query(): Error reading result set's header in /homepages/40/d127839664/htdocs/roygen/tngconnect.php on line 157An error has occurred in the TNG software. This could be due to a setup issue, an incomplete upgrade or a program bug. If you are the site owner, you may contact TNG support for help with this problem. Please copy the query below and paste it into your message.Query: SELECT p.personid, p.firstname, p.lastname FROM roytng_people p WHERE p.personid NOT IN ( SELECT p.personID FROM roytng_people p INNER JOIN roytng_events e ON p.personID = e.persfamID AND p.gedcom = e.gedcom INNER JOIN roytng_eventtypes et ON e.eventtypeID = et.eventtypeID WHERE et.tag = 'REVIEW' ) ORDER BY p.lastname, p.firstname MySQL server has gone away Regards, Rob Roy Quote Link to comment Share on other sites More sharing options...
Mark Posted April 14, 2019 Report Share Posted April 14, 2019 Are you able to run the SQL directly in phpMyAdmin and report back Quote Link to comment Share on other sites More sharing options...
Brett Posted April 14, 2019 Report Share Posted April 14, 2019 Rob Have you tried Mark's SQL on your pother tree? Quote Link to comment Share on other sites More sharing options...
Rob Roy Posted April 14, 2019 Author Report Share Posted April 14, 2019 1. Have no idea what phpMyAdmin is 2. Yes, added the event and query in the other database (pretty generic) and ran the query. Results: SQL: SELECT p.personid, p.firstname, p.lastname FROM tng_people p WHERE p.personid NOT IN ( SELECT p.personID FROM tng_people p INNER JOIN tng_events e ON p.personID = e.persfamID AND p.gedcom = e.gedcom INNER JOIN tng_eventtypes et ON e.eventtypeID = et.eventtypeID WHERE et.tag = 'REVIEW' ) ORDER BY p.lastname, p.firstname Warning: mysqli_query(): MySQL server has gone away in /homepages/40/d127839664/htdocs/delveefam/tngconnect.php on line 157Warning: mysqli_query(): Error reading result set's header in /homepages/40/d127839664/htdocs/delveefam/tngconnect.php on line 157 An error has occurred in the TNG software. This could be due to a setup issue, an incomplete upgrade or a program bug. If you are the site owner, you may contact TNG support for help with this problem. Please copy the query below and paste it into your message. Query: SELECT p.personid, p.firstname, p.lastname FROM tng_people p WHERE p.personid NOT IN ( SELECT p.personID FROM tng_people p INNER JOIN tng_events e ON p.personID = e.persfamID AND p.gedcom = e.gedcom INNER JOIN tng_eventtypes et ON e.eventtypeID = et.eventtypeID WHERE et.tag = 'REVIEW' ) ORDER BY p.lastname, p.firstname MySQL server has gone away Regards, Rob Roy Quote Link to comment Share on other sites More sharing options...
theKiwi Posted April 14, 2019 Report Share Posted April 14, 2019 8 hours ago, Rob Roy said: MySQL server has gone away This usually means that the server has timed out - i.e. the query took longer than the server's admin has allowed for a query to run. You might try contacting your hosting company about this and see if they will increase your time allowance. Roger Quote Link to comment Share on other sites More sharing options...
Rob Roy Posted April 14, 2019 Author Report Share Posted April 14, 2019 Makes sense. Will call them tomorrow. Thank you, everyone. Quote Link to comment Share on other sites More sharing options...
Rob Roy Posted April 15, 2019 Author Report Share Posted April 15, 2019 Alas, since I am in a shared hosting environment, they cannot change the timing on MySQL. I'm going to have to come up with a different solution. Anyone know if there is a way to create a custom event and have it added to every record in the database? 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.