search text in an event note

[Jean-Luc Lauzon]

How can I search a word or text in an event note only and not in all the notes?

Thanks

Jean-Luc

[GOGGS]

Select * from tng_events where info like '%some string%'

The tng_events table has the field 'info' (assuming that's what you mean), so if you write a report you could look for a string there.

 

GOGGS

 

[Jean-Luc Lauzon]

Hi,

Thanks for the answer. Since I'm not an SQL expert, what would be the correct search sentence if I want to search the world "Age" in all of the census event note and the person ID of who has this event?

Thanks

Jean-Luc

[GOGGS]

Assuming your eventtypes table has the word 'census' somewhere in the display of the event type, you could do this:

SELECT persfamID, tag, description, display
FROM tng_eventtypes t 
INNER JOIN tng_events e 
ON t.eventtypeID = e.eventtypeID
WHERE info like '%age%'
AND display LIKE = '%census%

or just drop the last line if the display does not include the word 'census' or you want to look for the 'age' string in all of the possible event types...

 

GOGGS

[Jean-Luc Lauzon]

Hi GOGGS

Thanks for your reply, and inspire from your lines I created a more complex query. It will show personID, date, info, note and source for event "Notaire" or "Justice". It can be use for any eventtype

SELECT C.persfamID, C.eventdatetr, C.info, A.note, S.title AS Source

FROM tng_xnotes A

JOIN tng_notelinks B ON A.ID = B.ID

JOIN tng_events C ON B.eventid = C.eventid

JOIN tng_eventtypes D ON C.eventtypeID = D.eventtypeID

LEFT JOIN tng_citations CI ON C.eventID = CI.eventID

LEFT JOIN tng_sources S ON CI.sourceID = S.sourceID

WHERE A.note LIKE '%%' AND (D.description LIKE '%Notaire%' OR D.description LIKE '%Justice%')

ORDER BY C.eventdatetr

[Jean-Luc Lauzon]

Hi,

What kind of SQL command I can do if I want, for example  to search a family having three brothers name Peter, Paul and Frank? 

Thanks

Jean-Luc

[GOGGS]

That's a tricky one, and I'm sure there are a lot of creative solutions. Anyway, using GROUP to combine children by family and then searching the family list for those names is maybe the cleanest way. It will not give you the 3 children even though it will find the family and you can get ID's for husband, wife, plus the familyID and lastname of the last child from your search of 3. This worked on my parent's family (with 4 kids) and I asked to find a family with 3 of the first names. So you would do this:

SELECT c.familyID, p.lastname, husband, wife 
FROM tng_children c INNER JOIN tng_people p ON c.personID = p.personID AND c.gedcom = p.gedcom 
INNER JOIN tng_families f ON c.familyID = f.familyID AND c.gedcom = f.gedcom 
WHERE firstname LIKE '%Peter%' OR firstname LIKE '%Paul%' OR firstname LIKE '%Frank%' 
GROUP BY c.familyID
HAVING COUNT(DISTINCT firstname) >= 3

 

GOGGS