Tree select sqlite

Question

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!

Answer 1

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.