Android Question SQL RECURSIVE family tree query

FERNANDO SILVEIRA

Active Member
Licensed User
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:

upload_2018-6-10_11-17-7.png


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
 

Attachments

  • FamilyDB.zip
    3.2 KB · Views: 264
Top