Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qsM2R-007ggY-PZ for pgsql-novice@arkaria.postgresql.org; Mon, 16 Oct 2023 11:45:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qsM2O-000Ogw-HF for pgsql-novice@arkaria.postgresql.org; Mon, 16 Oct 2023 11:45:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qsM2O-000Ogo-4k for pgsql-novice@lists.postgresql.org; Mon, 16 Oct 2023 11:45:32 +0000 Received: from mail-ej1-x633.google.com ([2a00:1450:4864:20::633]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qsM2K-000kHR-36 for pgsql-novice@lists.postgresql.org; Mon, 16 Oct 2023 11:45:31 +0000 Received: by mail-ej1-x633.google.com with SMTP id a640c23a62f3a-9c41e95efcbso131121166b.3 for ; Mon, 16 Oct 2023 04:45:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1697456726; x=1698061526; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=imo9c5ptDtt1NZ9k3aOi1Rb3ePNsUBmHA6Ohlf5fTfs=; b=ICGYmBb1tQYU4KGve33iPLc9XCgy5aGM5UL093PY45joRBzRxqeMdn03OwNuvmHTvQ cWOf2LUQi/H8zRjeV8z5dZm0WjzwroWXgJFl8Jv8eCSafD7dXz/1R8+f5rlUj1vggZ4K VDHxcrZYY/GzNLakJ9axAAshBCW6kIsXrXKV0q0Mc1+B2z+fkRBowEhEBJLC6JogINLM pk1w5h/UsHNbPHmkI2auEDtzNHmq8vOsMglEvpXV9XT2cmeHui2yerHv3OOKuj2RpKJ7 Nc+rT9Pedaq2mV1SOhjTrL1JhWgT4SXHBLHYOhqX7anfer7zGdMWUjAiW2BtndI64dcN AabA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1697456726; x=1698061526; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=imo9c5ptDtt1NZ9k3aOi1Rb3ePNsUBmHA6Ohlf5fTfs=; b=N0e7URUqyo2l94R0wgWr/IJ+IeWVso282jIpjbhApBIU5WGy9WFNjIquwcSf4d5XDq 9I2A4sRi1iO0/0VuAGQbZl0biacJ/2DA0WaR4BXuksye94LEeNx9nKGEm/+HnwycVfGr K1Ciklc6uRStZOl43+FpKl3RjFrUqBAgYB+TWsnTqzTZBxQPUX8a7EuyNTUPWdQq6ahM KlXjEj4fngRfnxNtvk97K0cArBRmJHuvWh79839zJfF6jLNssURWDc4K1bcXqSABPJET 9idX/5HH74XSphL5Qb8lOrwT5OWp3H8IDtmLEUVdI9FB6CQknR7FlPmvSHTrV+RCzLkC BBZw== X-Gm-Message-State: AOJu0YwKGW0zDkXTMdUlqpuN7cbYNsliOXI+tm7Rz6n4pFNvEO66Nli2 Upq+dy2DwB2wN5KgsqA9wtBYqj84C90ha26d25g= X-Google-Smtp-Source: AGHT+IFKw6aOGfpd9Xzm9it6ohX57En7cn9CjcCBJA0376Ydn7OtG43aqv3tBsC5mt24w7lufXaygsNtoiIH7v8Ujis= X-Received: by 2002:a17:907:980e:b0:9bf:1477:ad7c with SMTP id ji14-20020a170907980e00b009bf1477ad7cmr4745887ejc.11.1697456725471; Mon, 16 Oct 2023 04:45:25 -0700 (PDT) MIME-Version: 1.0 References: <1901532185.8873313.1696511707263@mail.yahoo.com> In-Reply-To: From: Ibrahim Shaame Date: Mon, 16 Oct 2023 14:44:35 +0300 Message-ID: Subject: Re: Reporting by family tree To: "David G. Johnston" Cc: swastik Gurung , "pgsql-novice@lists.postgresql.org" Content-Type: multipart/alternative; boundary="00000000000024b5f20607d3f132" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000024b5f20607d3f132 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 =3D mzazi.namba ORDER BY g.namba; Any suggestion? Thanks On Thu, Oct 5, 2023 at 6:03=E2=80=AFPM David G. Johnston wrote: > On Thu, Oct 5, 2023 at 7:54=E2=80=AFAM Ibrahim Shaame = 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 majina= 2) >> >> > 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. > > --00000000000024b5f20607d3f132 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks David for the reply. But I think you missed pa= rt of the code, which refers to ukoo:
)
=C2=A0
SELECT =C2= =A0g.jina AS jina_la_mtoto,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 g.baba AS baba_w= a_mtoto,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 g.babu AS babu_wa_mtoto,
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 g.namba,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 mzazi.jina AS = jina_la_mzazi,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 mzazi.baba AS jina_la_baba_la= _mzazi,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 g.daraja
FROM ukoo g
JOIN maji= na2 mzazi
ON g.namba =3D mzazi.namba
ORDER BY g.namba;

=
Any suggestion?
Thanks



On Thu, Oct 5, 2023 at 6:03=E2=80=AFPM David G. Johnston <david.g.johnston@gmail.com> wrot= e:
On Thu, Oct 5, 2023 at 7:54=E2=80=AFAM Ibrahim Shaame <ishaame@gmail.com> wrote:<= /span>
WITH RECURSIVE ukoo AS (
=C2= =A0 =C2=A0 SELECT namba,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0jina,
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0baba,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0b= abu,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0nasaba_1,
=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0daraja
=C2=A0 =C2=A0 FROM majina2
=C2=A0 =C2=A0 WHERE m= ajina2.nasaba_1 IN (SELECT DISTINCT namba FROM majina2)
=C2=A0
UNION = ALL
=C2=A0
=C2=A0 =C2=A0 SELECT mtoto.namba,
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0mtoto.jina,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0mtoto.baba,=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0mtoto.babu,
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0mtoto.nasaba_1,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0daraja
= =C2=A0 =C2=A0 FROM majina2 mtoto
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHER= E mtoto.nasaba_1 NOT IN (SELECT DISTINCT namba FROM majina2)

<= /div>

The reason it is called a "recursi= ve" CTE is that the subquery following the union all is recursive in n= ature - i.e., it should refer to itself.=C2=A0 You named the CTE ukoo but y= ou never actually refer to ukoo in the recursive subquery.=C2=A0 Thus, you = have not written a recursive query.

When you reference= the recursive "table" in the subquery its contents contain the r= esults 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.

--00000000000024b5f20607d3f132--