0

I am using sqlite in android and need to make an select to get all parent with their child's, for example:

id || parent_id || child_id || name

1     1            1           jhon
2     1            2           helen
3     2            3           barack
4     1            4           manuel
5     3            5           gaga

result should be: 

jhon
helen
manuel
barack
gaga

So, I need a reqursive sql, but googling a bit I found that CTE is not supported on sqlite, anyway I can use even an recursive java method to return a list of selected names order by parent asc

Notice that tree depth can be more than 2 levels!

Improve this question
0

1 Answer 1

Reset to default
0

I'm not sure how to interpret your table. Each node has an ID, right; and a (unique) parent_id (pointing to itself for the root node?). What's the child_id? Can't there be multiple children?

When dealing with recursive structures of arbitrary depth, if the tree doesn't change too often, and queries need to be fast, create a supporting table (say, "ancestral_closure") detailing the closure of all parent-child relationships:

ancestor_id, child_id

and make sure it's updated whenever the base table changes (recurse through the base table and add a row for each node that sits below another one). Join with the ancestral_closure table when you need to find all parents and/or children of a node. I don't think sqlite supports stored procedures executed on insert/delete/update triggers, so the update will have to be triggered by hand.

SQL is good at simple relations, not arbitrary graphs.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

id is prymary key autoincrement, but parent_id and child_id are not unique. It's a non orienteg graph 1 -> 1,2,4 || 2->3 || 3->5

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.