Problem with #recursive query
Moved from RootsMagic Facebook Group at Tom's suggestion.
I asked for help writing a recursive query to get ancestors and no of generations back (=level) and Tom pointed me here:
Welcome to the SQLite Users Club. I finf writing recursive queries daunting but fwiw, here are some of mine. https://sqlitetoolsforrootsmagic.com/tag/recursive/
I replied:
I can grasp some of the query but not all (see attached file).
I'm trying to add the level (i.e. how many generations back) and store in a new table with that as an added column.
Trying to base on the very simple example
CREATE TABLE org(
name TEXT PRIMARY KEY,
boss TEXT REFERENCES org
) WITHOUT ROWID;
INSERT INTO org VALUES('Alice',NULL);
INSERT INTO org VALUES('Bob','Alice');
INSERT INTO org VALUES('Cindy','Alice');
INSERT INTO org VALUES('Dave','Bob');
INSERT INTO org VALUES('Emma','Bob');
INSERT INTO org VALUES('Fred','Cindy');
INSERT INTO org VALUES('Gail','Cindy');
WITH RECURSIVE
under_alice(name,level) AS (
VALUES('Alice',0)
UNION ALL
SELECT org.name, under_alice.level+1
FROM org JOIN under_alice ON org.boss=under_alice.name
ORDER BY 2 DESC
)
SELECT level*3, substr('..........',1,level*3) || name FROM under_alice;
But a very simple equivalent - just as a query to get the data before turning into an INSERT crashes with access violation even if I put a LIMIT of 2 or 10 on it.
WITH RECURSIVE parent_of(PersonID, level) AS
(
VALUES (2,0)
UNION ALL
SELECT fm.FatherID, parent_of.level+1
FROM FamilyTable JOIN fm.ChildID = parent_of.PersonID
ORDER BY 2 DESC
)
SELECT * from parent_of;
I tried working out if I could add level to your query and change to insert into a different table, but could not work out how to do it.
Can anyone help?
Uploaded files: