Re: Reporting by family tree

Thanks David for the reply. But I think you missed part of the code, which
refers to ukoo:
)

SELECT  g.jina AS jina_la_mtoto,
        g.baba AS baba_wa_mtoto,
        g.babu AS babu_wa_mtoto,
        g.namba,
        mzazi.jina AS jina_la_mzazi,
        mzazi.baba AS jina_la_baba_la_mzazi,
        g.daraja
FROM ukoo g
JOIN majina2 mzazi
ON g.namba = mzazi.namba
ORDER BY g.namba;

Any suggestion?
Thanks



On Thu, Oct 5, 2023 at 6:03 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:

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