Re: Reporting by family tree
| От | David G. Johnston |
|---|---|
| Тема | Re: Reporting by family tree |
| Дата | |
| Msg-id | CAKFQuwaMRJUpMz8Usd3XHfymgdR=51G-CjOpi16nxWh+kytD0Q@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Reporting by family tree (Ibrahim Shaame <ishaame@gmail.com>) |
| Ответы |
Re: Reporting by family tree
|
| Список | pgsql-novice |
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.
В списке pgsql-novice по дате отправления: