Re: Reporting by family tree

On Thu, Oct 5, 2023 at 7:54 AM Ibrahim Shaame <ishaame(at)gmail(dot)com> wrote:

> WITH RECURSIVE ukoo AS (
> SELECT namba,
> jina,
> baba,
> babu,
> nasaba_1,
> daraja
> FROM majina2
> WHERE majina2.nasaba_1 IN (SELECT DISTINCT namba FROM majina2)
>
> UNION ALL
>
> SELECT mtoto.namba,
> mtoto.jina,
> mtoto.baba,
> mtoto.babu,
> mtoto.nasaba_1,
> daraja
> FROM majina2 mtoto
> WHERE mtoto.nasaba_1 NOT IN (SELECT DISTINCT namba FROM majina2)
>
>
The reason it is called a "recursive" CTE is that the subquery following
the union all is recursive in nature - i.e., it should refer to itself.
You named the CTE ukoo but you never actually refer to ukoo in the
recursive subquery. Thus, you have not written a recursive query.

When you reference the recursive "table" in the subquery its contents
contain the results of the previous iteration, that is what allows you to
select a child record and then consider that record a parent when finding
the next depth/layer of children.

David J.