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 1qoPfi-001AK0-KB for pgsql-novice@arkaria.postgresql.org; Thu, 05 Oct 2023 14:49:51 +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 1qoPff-00EK0C-Hg for pgsql-novice@arkaria.postgresql.org; Thu, 05 Oct 2023 14:49:48 +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 1qoPff-00EK03-6F for pgsql-novice@lists.postgresql.org; Thu, 05 Oct 2023 14:49:48 +0000 Received: from mail-ej1-x62b.google.com ([2a00:1450:4864:20::62b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qoPfY-000Bg4-Ml for pgsql-novice@lists.postgresql.org; Thu, 05 Oct 2023 14:49:47 +0000 Received: by mail-ej1-x62b.google.com with SMTP id a640c23a62f3a-99c136ee106so196389866b.1 for ; Thu, 05 Oct 2023 07:49:40 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1696517379; x=1697122179; 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=pTE3AmnbkDsyYFVf5c3/x1bwcfoE7avZV2dU+wTJcE0=; b=YLLW9gl0a9uALaZsmIMAPfe5fpglJbqqyNzfMW5jEST1akAAGTysa3J1qRJp3nrCgh zSCY0ZuAmH6uvXVISzLg9ob8WXS8qNUx1EqmOMy3IvIgko7QK7s4FABLBJs1Q0Uv/Yft HxYpp5bxJZx1To0dfAd+2d4h/GiHaGP2MgSMrJ+dE5aFd+tA3CZU+LiZ87C4wBc+wyMF heLJnJsgBuUJ05/mmcJWjwpeaq7/isJ7Bf2LrQ63LSAiBU/EqqNH66F/3yb2yGI+7SAr Z3+l+i/IZfWGEKkZOvKejB/frCUXPrvZ22RDFM+SfliMXDtV+X0UkrPZP6RssPq9gnDA K0Sw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1696517379; x=1697122179; 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=pTE3AmnbkDsyYFVf5c3/x1bwcfoE7avZV2dU+wTJcE0=; b=qFtmxPjTZQgxsXoqgqrFOxtD7M1IRt1NBulQYDHCtlgRZcz1AKhzmCpu4TUDcqxyEZ yCw36uGDyi7iq/757AL7gjRI2I9+9UveZQIkvIF+FhlUYsstQbucuPe0MjnZKTImhS5h 1mnLWkd/Yy6/jg5ewm7SD3I+p29VBFRvZWO9HAvDH/JrHtkJDrJ6li1FvIPpvtTwc8It ewZ7Mpj/wkI0qLCDu0P5X5qBVQRzmp6JUiM/FlWw/2ztg6+3G0dGvtJ36JmJT/bdkazr 1SDjBEwrleL9OAv5CB0ctUZm8hO2rnFtfit67n1oj3qq2ok3Lddonv57XuuKy/pmaS+I jUqQ== X-Gm-Message-State: AOJu0Yzf4eCG+S2v2E3J+ThFOKibWVJ9RI41SlGdxi+9b4b2mzZRE6SI CCpVbZdoJanIEfZXgBvCb3qBGBNZswJ/TybtoP/HJ5v6oE4= X-Google-Smtp-Source: AGHT+IHp0Yj8Rh6aIS/1NkjAcLb2hoNejfOIzkvPlhPN+iL3vQo2sL5sQjNeIiaZIHTOoPUMLfXJCe57T/4n1zXN6I4= X-Received: by 2002:a17:906:530b:b0:9a2:1e03:1573 with SMTP id h11-20020a170906530b00b009a21e031573mr5611722ejo.65.1696517378859; Thu, 05 Oct 2023 07:49:38 -0700 (PDT) MIME-Version: 1.0 References: <1901532185.8873313.1696511707263@mail.yahoo.com> In-Reply-To: <1901532185.8873313.1696511707263@mail.yahoo.com> From: Ibrahim Shaame Date: Thu, 5 Oct 2023 17:48:56 +0300 Message-ID: Subject: Re: Reporting by family tree To: swastik Gurung Cc: "pgsql-novice@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000b8e6d40606f93b4c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b8e6d40606f93b4c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Swastik, thank you for the response. I started there, and have been stuck for many months now. I managed to get only father-child did not get further. Here is the example sql where I get father-child results. With this code: 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) ) 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; I get: jina_la_mtoto baba_wa_mtoto babu_wa_mtoto namba jina_la_mzazi jina_la_baba_la_mzazi daraja ---------------+---------------+---------------+--------+---------------+--= ---------------------+-------- Ibrahim Khamis Haji 100001 Ibrahim Khamis 6 Asia Khamis Haji 100002 Asia Khamis 6 Zubeir Khamis Haji 100003 Zubeir Khamis 6 Asha Mwinyi Bakari 100004 Asha Mwinyi 6 Mariama Mwinyi Bakari 100005 Mariama Mwinyi 6 Zainab Ibrahim Khamis 100006 Zainab Ibrahim 7 Fatma Ibrahim Khamis 100007 Fatma Ibrahim 7 Shaban Ibrahim Khamis 100162 Shaban Ibrahim 7 Alicia Shaban Ibrahim 100163 Alicia Shaban 8 Ideally I should get Ibrahim (father of Shaban) then Shaban the father of Alicia) Under Shaban should get Alicia Then Zainab (sister of Shaban Then Fatma (sister of Shaban) Then another member (after I have got Ibrahim and his descendants) Any idea? On Thu, Oct 5, 2023 at 4:15=E2=80=AFPM swastik Gurung wrote: > I suppose you ought to be using, recursive CTE queries > > Documentation can be found at: 7.8. WITH Queries (Common Table > Expressions) > > > 7.8. WITH Queries (Common Table Expressions) > > 7.8. WITH Queries (Common Table Expressions) # 7.8.1. SELECT in WITH > 7.8.2. Recursive Queries 7.8.3. Common Tabl... > > > > > > On Thursday, 5 October 2023 at 16:44:46 GMT+5:45, Ibrahim Shaame < > ishaame@gmail.com> wrote: > > > I have a table of members of a large family extendending back to eight > generations. The current members contribute a monthly amount to the famil= y > fund. Only true descendants are included in the family list, no wives, no > husbands. There are two tables > > 1 - Names with the following fields: idno (unique) --family member > > parentid -- id number of the parent who connected the child to the family > > etc > > etc > > 2 =E2=80=93 Contributions with fields: idno > > etc > > etc > > > Now I want to report Names and contributions par family tree: My ideal is > to list grandfather, father, children based on the two fields (id, > parentid). > > Any suggestions? > > Thanks in advance > > > --000000000000b8e6d40606f93b4c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Swastik, thank you for the response. I started there,= and have been stuck for many months now. I managed to get only father-chil= d did not get further. Here is the example sql where I get father-child res= ults.
With this code:
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 W= HERE 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=A0daraj= a
=C2=A0 =C2=A0 FROM majina2 mtoto
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= WHERE mtoto.nasaba_1 NOT IN (SELECT DISTINCT namba FROM majina2)
=C2=A0=
)
=C2=A0
SELECT =C2=A0g.jina AS jina_la_mtoto,
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 g.baba AS baba_wa_mtoto,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 g.bab= u 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.d= araja
FROM ukoo g
JOIN majina2 mzazi
ON g.namba =3D mzazi.nambaORDER BY g.namba;

I get:

=09 =09 =09 =09 =09
jina_la_mtoto baba_wa_mtoto babu_wa_mtoto namba jina_la_mzazi jina_la_baba_la_mzazi daraja
---------------+---------------+---------------= +--------+---------------+-----------------------+--------





Ibrahim Khamis Haji 100001 Ibrahim Khamis 6
Asia Khamis Haji 100002 Asia Khamis 6
Zubeir Khamis Haji 100003 Zubeir Khamis 6
Asha Mwinyi Bakari 100004 Asha Mwinyi 6
Mariama Mwinyi Bakari 100005 Mariama Mwinyi 6
Zainab Ibrahim Khamis 100006 Zainab Ibrahim 7
Fatma Ibrahim Khamis 100007 Fatma Ibrahim 7







Shaban Ibrahim Khamis 100162 Shaban Ibrahim 7
Alicia Shaban Ibrahim 100163 Alicia Shaban 8

Ideally I should get

Ibr= ahim (father of Shaban)
then Shaban the father of Alicia)
=
Under Shaban should get Alicia
Then Zainab (sister= of Shaban
Then Fatma (sister of Shaban)
Then another m= ember (after I have got Ibrahim and his descendants)

Any idea?



On Thu, Oct 5, 2023 at = 4:15=E2=80=AFPM swastik Gurung <gurung_swastik@yahoo.com> wrote:
I suppose you ought to be using, recursive CTE que= ries

Documentation can be = found at:=C2=A07.8. WI= TH Queries (Common Table Expressions)

=20
On Thursday, 5 October 2023 at 16:44:46 GMT+5:45, Ibrah= im Shaame <ishaam= e@gmail.com> wrote:


=09 =09 =09

I have a table of members of a large family extendending back to eight generations. The current members contribute a monthly amount to the family fund. Only true descendants are included in the family list, no wives, no husbands. There are two tables

1 - Names with the following fields: idno (unique) --family member

parentid -- id number of the parent who connected the child to the family

etc

etc

2 =E2=80=93 Contributions with fields: idno

etc

etc


Now I want to report Names and contributions par family tree: My ideal is to list grandfather, father, children based on the two fields (id, parentid).

Any suggest= ions?

Thanks in advanc= e


--000000000000b8e6d40606f93b4c--