Sql to join people / events / to media/citiations
Quote from thejerrybryan on 2024-02-18, 12:43 pm
Sometimes I think the bane of my existence in dealing with EventTable is dealing with this distinction in a reasonable fashion.
Yes -- so I want for not to focus on individuals get both nulls and non nulls without getting false positives for other things due the way the ownership work.
I could exclude FAMILY facts but that still would not likely go father enough. Simple ask but more complicated solution as it turns out.
Reworked below -- not sure about postives but the result look is what I was looking for: (basically I added the names only to review if results looked right)
-- all Facts / place by person and Place, Media, Citation
select pt.PersonID, nt.Surname, nt.Given, substr(et.Date, 4,4), et.EventID, ftt.Name, plt.PlaceID, ml.MediaID, clt.CitationID
from nameTable as nt
join PersonTable as pt on nt.OwnerID = pt.PersonID
join EventTable as et on et.OwnerID = pt.PersonID
join FactTypeTable as ftt on et.EventType = ftt.FactTypeID
LEFT join PlaceTable as plt on et.PlaceID = plt.PlaceID
LEFT join MediaLinkTable AS ml ON ml.OwnerType = 2 AND ml.OwnerID = et.EventID
LEFT join CitationLinkTable as clt ON clt.OwnerType = 2 AND clt.OwnerID = et.EventID
Where et.OwnerType =0
Uploaded files:
