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.