public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ibrahim Shaame <[email protected]>
To: David G. Johnston <[email protected]>
Cc: swastik Gurung <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Reporting by family tree
Date: Mon, 16 Oct 2023 14:44:35 +0300
Message-ID: <CAJOWwD6ED658ZAg80YK85sFq=vObnrj6f086tNug6C8=K7wn3w@mail.gmail.com> (raw)
In-Reply-To: <CAKFQuwaMRJUpMz8Usd3XHfymgdR=51G-CjOpi16nxWh+kytD0Q@mail.gmail.com>
References: <CAJOWwD5M=YK9nKBX6TD-NB1B7Ddo6uLZC2fG1WAED86fg4s2oA@mail.gmail.com>
	<[email protected]>
	<CAJOWwD7YmD8jPediD7qG49vq9mtTbJOx3eAV4a9234RTJe_aHw@mail.gmail.com>
	<CAKFQuwaMRJUpMz8Usd3XHfymgdR=51G-CjOpi16nxWh+kytD0Q@mail.gmail.com>

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 <[email protected]>
wrote:

> 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: <CAJOWwD6ED658ZAg80YK85sFq=vObnrj6f086tNug6C8=K7wn3w@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