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