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

Are you looking to do this 100% in sql or are you calling sql from some other language? What database are you using? What have you tried so far? — John, Aug 07 '15 at 15:31
Try use dedicated type for it - like hierarchyid https://msdn.microsoft.com/ru-ru/library/bb677290(v=sql.120).aspx. SQL is about sets - tell me if you need an example — Yurii Hohan, Aug 07 '15 at 16:56

score 2 · Answer 1 · answered Aug 07 '15 at 15:18

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).

I can't know how many levels of childs I can have. This is dynamic. — Laurianti, Aug 07 '15 at 15:30

score 2 · Accepted Answer · edited May 23 '17 at 11:59

2

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

edited May 23 '17 at 11:59

Community

1
1

answered Aug 07 '15 at 15:35

John

3,458
4
33
54

score 1 · Answer 3 · answered Sep 15 '15 at 07:35

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.

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

3 Answers3