Android Question SQL RECURSIVE family tree query

[FERNANDO SILVEIRA]

Hello guys,

I know this may not be the correct place to put this question but, since many of you have more SQL knowledge than me, I'll give it a try...

I have this test FamilyDb SQLite database (attached), I adapted the following query from an example I saw and it works fine to list DECENDANTS of a given person (ie, 'BENEDITA'). It lists the person and his/her LEVEL on the hierarchy.

WITH RECURSIVE under_someone(name,level) AS (VALUES('BENEDITA',0)
UNION ALL
SELECT family.name, under_someone.level + 1 FROM family
JOIN under_someone ON (family.dad = under_someone.name or family.mom = under_someone.name) ORDER BY 2 DESC)
SELECT substr('....................',1,level*4) || name as Person, * FROM under_someone ​

Which produces the following results:

Now I need a similar SQL query that lists the ANCESTORS of a given person (ie, 'MARIA JOANA') also listing their LEVEL on this hierarchy.

I have this other query which is working partially, but not as I expect (I was unable to include LEVEL field as above).

WITH RECURSIVE parent_of(name, parent) AS
(SELECT name, mom FROM family
UNION
SELECT name, dad FROM family), someone(name) AS
(SELECT parent FROM parent_of WHERE name = 'MARIA JOANA'
UNION ALL
SELECT parent FROM parent_of JOIN someone USING(name))

SELECT family.name, BORN, * FROM someone, family WHERE someone.name = family.name ORDER BY born desc;​

Can someone help me on this SQL issue?

Regards,
Fernando