public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ibrahim Shaame <[email protected]>
To: swastik Gurung <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Reporting by family tree
Date: Thu, 5 Oct 2023 17:48:56 +0300
Message-ID: <CAJOWwD7YmD8jPediD7qG49vq9mtTbJOx3eAV4a9234RTJe_aHw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAJOWwD5M=YK9nKBX6TD-NB1B7Ddo6uLZC2fG1WAED86fg4s2oA@mail.gmail.com>
	<[email protected]>

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 = 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 PM swastik Gurung <[email protected]>
wrote:

> I suppose you ought to be using, recursive CTE queries
>
> Documentation can be found at: 7.8. WITH Queries (Common Table
> Expressions)
> <https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-RECURSIVE;
>
> 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...
>
> <https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-RECURSIVE;
>
>
>
> On Thursday, 5 October 2023 at 16:44:46 GMT+5:45, Ibrahim Shaame <
> [email protected]> 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 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 – 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
>
>
>

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]
  Subject: Re: Reporting by family tree
  In-Reply-To: <CAJOWwD7YmD8jPediD7qG49vq9mtTbJOx3eAV4a9234RTJe_aHw@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