Jump to content
TNG Community
Sign in to follow this  
jayat1familytree

Sql Query help for searching notes

Recommended Posts

jayat1familytree
Need a second set of eyes to catch the error on why I am not getting any records found.
 
Looking to find notes with the phrase "buried in" or "internment" where the person is deceased and their burialplace is empty.
 
I know the database should find over 300 records.
 
Here is the SQL

SELECT p.living, p.personID, p.lastname, p.firstname, p.birthdate, p.deathdate, p.burialdate, p.burialplace FROM tng_people p
JOIN tng_events e ON e.persfamID = p.personID
JOIN tng_notelinks nl ON p.personID = nl.persfamID
JOIN tng_xnotes x ON x.noteID = nl.xnoteID AND nl.persfamID = p.personID
WHERE p.living = 0 AND p.burialplace = ' '  AND (x.note LIKE '%buried%' OR x.note LIKE '%internment%')
ORDER BY p.lastname, p.firstname

 

Thanks in advance for any help.

Share this post


Link to post
Share on other sites
jayat1familytree

Resolved,

I modified a different report to get a satisfactory result.

The code is; (change e.eventtypeID = 130  to whatever ID number you have for Obituary Events.)

 

SELECT p1.personID, p1.lastname, p1.firstname,
   IF(p1.sex='F' AND p2.lastname IS NOT NULL, p2.lastname, ' ') AS MarriedName,
   p1.birthdate AS Birth_Date_, p1.deathdate AS Death_Date_,
   p1.burialdate AS Burial_Date_, p1.burialplace AS Burial_Place_Location, x1.note AS Note
FROM tng_people p1
LEFT OUTER JOIN tng_families f1 ON p1.gedcom = f1.gedcom AND p1.personID = f1.wife
LEFT OUTER JOIN tng_people p2 ON f1.gedcom = p2.gedcom AND f1.husband = p2.personID
LEFT OUTER JOIN tng_notelinks n1 ON p1.gedcom = n1.gedcom AND p1.personID = n1.persfamID
LEFT OUTER JOIN tng_xnotes x1 ON n1.gedcom = x1.gedcom AND n1.xnoteID = x1.ID
LEFT OUTER JOIN tng_events e ON e.gedcom = n1.gedcom AND e.eventID = n1.eventID
LEFT OUTER JOIN tng_medialinks m1 ON p1.gedcom = m1.gedcom AND p1.personID = m1.personID AND (e.eventtypeID = '130')
LEFT OUTER JOIN tng_media M2 ON m1.mediaID = M2.mediaID
LEFT OUTER JOIN tng_mediatypes M3 ON M2.mediatypeID = M3.mediatypeID
WHERE (p1.living = 0) AND (p1.burialdate = "" AND p1.burialplace = "") AND (x1.note LIKE '%buried%' OR x1.note LIKE '%interment%')
ORDER BY p1.lastname, p1.firstname

 

 

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
Sign in to follow this  

×