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(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

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