Jump to content
TNG Community
Rob Roy

Missing Event Report

Recommended Posts

Rob Roy

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.

Share this post


Link to post
Share on other sites
JPB

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

other-event.png

Share this post


Link to post
Share on other sites
Rob Roy

Nice feature but doesn't do what I need.  I entered as follows:

image.png

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.

Share this post


Link to post
Share on other sites
Ken Roy

Have you checked the TNG Wiki Reports category ?

Share this post


Link to post
Share on other sites
Rob Roy

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

Share this post


Link to post
Share on other sites
Brett

Rob

Are you able to provide the SQL you have used please?

Share this post


Link to post
Share on other sites
Rob Roy

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

Share this post


Link to post
Share on other sites
Mark

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

Share this post


Link to post
Share on other sites
Rob Roy

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 157

Warning: mysqli_query(): Error reading result set's header in /homepages/40/d127839664/htdocs/roygen/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 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

 

Share this post


Link to post
Share on other sites
Mark

Are you able to run the SQL directly in phpMyAdmin and report back

Share this post


Link to post
Share on other sites
Brett

Rob

Have you tried Mark's SQL on your pother tree?

Share this post


Link to post
Share on other sites
Rob Roy

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 157

Warning: 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

Share this post


Link to post
Share on other sites
theKiwi
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

Share this post


Link to post
Share on other sites
Rob Roy

Makes sense.  Will call them tomorrow.

 

Thank you, everyone.

Share this post


Link to post
Share on other sites
Rob Roy

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?

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

×