SQLite Tools for RootsMagic

Exploit your RootsMagic family tree database with SQLite Tools

List all events in eventable with facts > 1

1 vote

List all events in eventable with facts > 1

I am looking for a query that will give me the ownerid, surname and given, fact name & year. I have duplicate Births, deaths, residences, burials, and more. I need to count the records and give me the ones that are > 1. I have this query which has extra fields displayed to verify myself and to tweak the query. I want every record in the eventtable. I am not getting any alternate names or marriages. I have been away from queries for a while and have forgotten a lot. I know this query displays both the maiden name and the married name. That is ok because I rerun the query quite often so the doubles will drop off. I would rather have both vs missing some. If there is a better way to accomplish this, by all means do it. – All Facts greater than 1 select et.ownerid, nt.surname || ", " || nt.given as Name, ft.name || " – " || substr(et.date,4,4) as type, nt.nameid || " – " || et.eventtype || " – " || substr(et.date,4,4) || "/" || substr(et.date,8,2) || "/" || substr(et.date,10,2) as fact, substr(et.date,4,8), nt.nameid, et.eventtype as etype, substr(et.date,4,4) as date, count(nt.nameid || "-" || et.eventtype || " – " || substr(et.date,4,4) || "/" || substr(et.date,8,2) || "/" || substr(et.date,10,2)) as counts from eventtable et –nametable nt, –facttypetable ft join nametable nt on et.ownerid = nt.ownerid join facttypetable ft on et.eventtype = ft.facttypeid where nt.nametype = 0 –and et.ownerid = nt.ownerid –and et.eventtype = ft.facttypeid group by fact having count(nt.nameid || "-" || et.eventtype || " – " || substr(et.date,4,4) || "/" || substr(et.date,8,2) || "/" || substr(et.date,10,2)) > 1 order by nt.surname || ", " || nt.given, type;

momakid momakid shared this idea
← All Requests

2 thoughts on “List all events in eventable with facts > 1”

  1. The request form destroyed whatever formatting there was and collapsed your sql into a single line. Tried running it as is but it errored out. So I threw it into an AI Chat to reformat and it identified a bunch of errors and spat out the following which, to my surprise, actually worked, I think as you intended. Here’s one result (which may be misrendered once posted):

    OwnerID Name type fact SUBSTR(et.date, 4, 8) NameID etype date counts
    819 Decker, John Gilbert Birth – 1861 857 – 1 – 1861/11/18 18611118 857 1 1861 2

    Here’s the formatted sql:

    -- All Facts greater than 1
    SELECT
    et.ownerid,
    nt.surname || ", " || nt.given AS Name,
    ft.name || " – " || SUBSTR(et.date, 4, 4) AS type,
    nt.nameid || " – " || et.eventtype || " – " || SUBSTR(et.date, 4, 4) || "/" || SUBSTR(et.date, 8, 2) || "/" || SUBSTR(et.date, 10, 2) AS fact,
    SUBSTR(et.date, 4, 8),
    nt.nameid,
    et.eventtype AS etype,
    SUBSTR(et.date, 4, 4) AS date,
    COUNT(nt.nameid || "-" || et.eventtype || " – " || SUBSTR(et.date, 4, 4) || "/" || SUBSTR(et.date, 8, 2) || "/" || SUBSTR(et.date, 10, 2)) AS counts
    FROM eventtable et
    JOIN nametable nt
    ON et.ownerid = nt.ownerid
    JOIN facttypetable ft
    ON et.eventtype = ft.facttypeid
    WHERE
    nt.nametype = 0
    GROUP BY
    fact
    HAVING
    COUNT(nt.nameid || "-" || et.eventtype || " – " || SUBSTR(et.date, 4, 4) || "/" || SUBSTR(et.date, 8, 2) || "/" || SUBSTR(et.date, 10, 2)) > 1
    ORDER BY
    nt.surname || ", " || nt.given,
    type;

    Your request says something about Alternate Names and Marriage events. I’m guessing that you are missing them. That’s because:

  2. Alternate Names are not in the EventTable as other fact types are but are in the NameTable with IsPrimary=0 (1 is the Primary Name). So you might be getting Alt Name instead of the Primary Name for some people and vice-versa for others.
  3. Marriage and other family-type fact type events are linked to the FamilyID, not the PersonID, so that both spouses can be found. So your query is likely bringing out the name of a person who is not one of the actual spouses.

    4. You might want to look back at some posts that have been made about listing “All Facts for a Person” to see if you can adapt one to your purpose.

Comments are closed.