Select all genealogy of a father in SQL without WITH or WHILE

Question

Is it possibile to select all childs of a parent without recursive system as WITH or WHILE?

The structure is:

Father  Child

NULL    1
1       2
1       3
2       4
3       5
4       6
6       7

I want the "childs" (all genealogy, childs of childs, etc. etc.) of 2, so I want:

4, 6, 7

Answer 1

Yes. Every recursive function has a non-recursive solution:

Can every recursion be converted into iteration?

Some other resources that might be helpful:

http://www.slideshare.net/rs_rs123/removal-of-recursion

http://www.refactoring.com/catalog/replaceRecursionWithIteration.html

Answer 2

If you know precisely what depth you want to go to, then sure, you don't have to use recursion. For example, to find the first-level descendants of a given parent, just do:

select Child
from MyTable
where Father = 2

Even if you want multiple levels (grandchildren, grandparents, etc.), as long as you know how many levels you want, you don't strictly need recursion, you can just nest multiple inline views like this:

select t1.Child
from MyTable t1
where t1.Father = 2
or t1.Father in (
    select t2.Child
    from MyTable t2
    where t2.Father = 2
)

(This gets children and grandchildren)

However, anytime you don't know how many levels up/down a tree you want to go (e.g. all descendants), recursion is generally the preferred, and sometimes the only recourse (pun intended).

Answer 3

My own approach here has been to do one query per generation.

So you would start from the child's id. For the first generation, select all persons whose child has the given id. For the second generation, select all persons whose child has an id in the first generation. And so on.

This significantly reduces the number of queries compared to finding the parents for each person recursively, and is extremely fast in the tests I ran on sqlite, for a genealogy of 22 generations.