Re: Reporting by family tree

On Thu, Oct 5, 2023 at 7:54 AM Ibrahim Shaame <ishaame@gmail.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.