Exploit your RootsMagic family tree database with SQLite Tools
Just learned of some interesting things about SQLite in this article that popped up in my Google News feed: In Praise of SQLite by Nikos Vaggalis, dated 28 July 2022. The latest version of SQLite is 3.39.2. I don’t monitor releases so this story made me aware of some significant changes delivered in 3.39.0 and…
Common Table Expressions (CTE) are a feature that was first introduced to SQL in about 2005 and provide a method of defining a temporary result set then using that in the query as if it were another table.
I am having trouble setting up this query. I want the results of the first query to merge/join/union with the second query where the ownerid from the first query equals the ownerid from the second query. The bold line should not be part of the query. I put it in to get the records from…
I don’t post here as often as I probably should. I find that most of my SQL queries are quite specific to my own personal research and are of little general interest. On the other hand, Tom has a genius for coming up with queries that are of very general utility. The queries I’m going…
I have been using this query for a quite a while now: — Alternate Name Typos Select f.FamilyID, — 1 — Column B Gets the surname, given name and fathered for the fatherid from the famillytable for the fathers primary record. This is the husband. (select n1.surname || “, ” || n1.given || ” ”…
You need a SQLite manager such as SQLiteSpy to read and modify a RootsMagic database using the queries or scripts from this wiki. That’s because RootsMagic incorporates the SQLite 3 database engine to write and read its database files. SQLite acts on the database in response to SQL statements or commands it is given. These…
This page has not been updated since 2012 but many scripts have been added since. The tag cloud on the home page and the search control are two good ways to find pages of interest. RootsMagic Queries Many of these were developed around RootsMagic 4 and should also work on RootsMagic 5 and 6 with…
Here’s a pretty dumb question. Why will the following extremely simple query not return the desired results? I’m trying to see the sentence templates just for the birth, death, marriage, and burial facts. SELECT FT.FactTypeID, FT.Name, FT.Sentence FROM FactTypeTable AS FT WHERE FT.Name IN (‘Birth’,’Death’,’Marriage’,’Burial’); To get the desired results, I instead do something like…