public inbox for [email protected]  

help / color / mirror / Atom feed

From: David G. Johnston <[email protected]>
To: Ibrahim Shaame <[email protected]>
Cc: swastik Gurung <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Reporting by family tree
Date: Thu, 5 Oct 2023 08:02:43 -0700
Message-ID: <CAKFQuwaMRJUpMz8Usd3XHfymgdR=51G-CjOpi16nxWh+kytD0Q@mail.gmail.com> (raw)
In-Reply-To: <CAJOWwD7YmD8jPediD7qG49vq9mtTbJOx3eAV4a9234RTJe_aHw@mail.gmail.com>
References: <CAJOWwD5M=YK9nKBX6TD-NB1B7Ddo6uLZC2fG1WAED86fg4s2oA@mail.gmail.com>
	<[email protected]>
	<CAJOWwD7YmD8jPediD7qG49vq9mtTbJOx3eAV4a9234RTJe_aHw@mail.gmail.com>

On Thu, Oct 5, 2023 at 7:54 AM Ibrahim Shaame <[email protected]> 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.

view thread (13+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Reporting by family tree
  In-Reply-To: <CAKFQuwaMRJUpMz8Usd3XHfymgdR=51G-CjOpi16nxWh+kytD0Q@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox