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 1qoPsY-001BA0-DC for pgsql-novice@arkaria.postgresql.org; Thu, 05 Oct 2023 15:03:06 +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 1qoPsW-00Ee6s-9a for pgsql-novice@arkaria.postgresql.org; Thu, 05 Oct 2023 15:03:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qoPsV-00Ee6H-VZ for pgsql-novice@lists.postgresql.org; Thu, 05 Oct 2023 15:03:04 +0000 Received: from mail-oo1-xc31.google.com ([2607:f8b0:4864:20::c31]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qoPsU-000BqW-Jx for pgsql-novice@lists.postgresql.org; Thu, 05 Oct 2023 15:03:04 +0000 Received: by mail-oo1-xc31.google.com with SMTP id 006d021491bc7-57b74782be6so587308eaf.2 for ; Thu, 05 Oct 2023 08:03:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1696518180; x=1697122980; 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=BNUv7OBPzLfaWc+uNjnSto0+jzGl5Ux7GNBPBs2psn0=; b=BC+sa95b/NTiTOsUzW0K4ngTGdUImwyELMWwiNobyweutjBeO9572V73DVVWDORK5t sIGKFeuRktJfrLhXD4KXpNqsGMpha2Ga0m0E21qcb1/P2qNNqM/pU+yJLXPtJ3UwIjvb 4uBdwhizAwVM3dl5S9usj+Rouh+vk34wNsLdP1nQo8ZGBkr8AlBtSLifQwliGtlD0zXX iQRW9JOrbdZ5vWKUP38x5ouAWXBgkUOnPnXvJUZ9ubsLTXwSXsm2dolkt9ai7aTyxy1p O+wyUix4TecsidMnfiIfyOpMH91D3qzzpm8hfn0aAZDMu0YDbKqSYKc0puNidRcPaUL3 JRhQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1696518180; x=1697122980; 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=BNUv7OBPzLfaWc+uNjnSto0+jzGl5Ux7GNBPBs2psn0=; b=I7ufvdkg96f4qR/J6rokNl0VQmEXBp9HpuBT2eKxtnPducpUVlnAA00V+5RvclzcDh puEkSKiOOrqlzI8NzM4G1/z8wsN0CMHZG+iOGr84hi5oM153e0N5UloqQK+BtAzHc+Sq Bpi9PZftYkroUFrUFZFyVVKSgorWOaUsjZ2wy4LIoAdCUUbaWsC8ILjqn0cKGbcSgLnF quBMGTZhNhtlm8jrGRPWwOG7WKhIsu4Gvfbopwj/yd8gQECK4fp3GHpsUmPsTmAxKiFB Sca7Og9v9aOBdv2EknOngpQrDe5Eluh4nKpanyyI6c0xLKfK4AFzPgP4EZVqIN2bE08V RUoA== X-Gm-Message-State: AOJu0YzJ/SAmOzAcYG//T/YTBMazQn1ewIpRz9J9BE+tCNVMHZvqkX46 j+FzgIMJ/R4C7S0X9i639w39woC9++H69OXfl20= X-Google-Smtp-Source: AGHT+IF+Hgb6pN+nxwMPQZxivkOtrqa0wxdjEXABq/dVXcwzH7ukf0eiQxTQ4185DWxt0+AuL1cv2cndl2wgQ7EFwe4= X-Received: by 2002:a4a:9d17:0:b0:57b:7145:310f with SMTP id w23-20020a4a9d17000000b0057b7145310fmr4945664ooj.0.1696518180534; Thu, 05 Oct 2023 08:03:00 -0700 (PDT) MIME-Version: 1.0 References: <1901532185.8873313.1696511707263@mail.yahoo.com> In-Reply-To: From: "David G. Johnston" Date: Thu, 5 Oct 2023 08:02:43 -0700 Message-ID: Subject: Re: Reporting by family tree To: Ibrahim Shaame Cc: swastik Gurung , "pgsql-novice@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000817feb0606f96b8e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000817feb0606f96b8e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Oct 5, 2023 at 7:54=E2=80=AFAM Ibrahim Shaame w= rote: > 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. --000000000000817feb0606f96b8e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Oct 5, 2023 at 7:54=E2=80=AFAM Ibrahim Shaame <= ishaame@gmail.com> wrote:
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=A0babu,=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 majina2.= 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 WHERE mtot= o.nasaba_1 NOT IN (SELECT DISTINCT namba FROM majina2)

<= /blockquote>

The reason it is called a "recursive&quo= t; CTE is that the subquery following the union all is recursive in nature = - i.e., it should refer to itself.=C2=A0 You named the CTE ukoo but you nev= er actually refer to ukoo in the recursive subquery.=C2=A0 Thus, you have n= ot written a recursive query.

When you reference the r= ecursive "table" in the subquery its contents contain the results= of the previous iteration, that is what allows you to select a child recor= d and then consider that record a parent when finding the next depth/layer = of children.

David J.

--000000000000817feb0606f96b8e--