Jump to content
TNG Community

Missing Event Report


Rob Roy

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...