public inbox for [email protected]
help / color / mirror / Atom feedReporting by family tree
13+ messages / 4 participants
[nested] [flat]
* Reporting by family tree
@ 2023-10-05 10:58 Ibrahim Shaame <[email protected]>
2023-10-05 13:15 ` Re: Reporting by family tree swastik Gurung <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Ibrahim Shaame @ 2023-10-05 10:58 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Reporting by family tree
2023-10-05 10:58 Reporting by family tree Ibrahim Shaame <[email protected]>
@ 2023-10-05 13:15 ` swastik Gurung <[email protected]>
2023-10-05 14:48 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: swastik Gurung @ 2023-10-05 13:15 UTC (permalink / raw)
To: [email protected] <[email protected]>; Ibrahim Shaame <[email protected]>
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 <[email protected]> wrote:
I have a table of members of alarge family extendending back to eight generations. The currentmembers contribute a monthly amount to the family fund. Only truedescendants 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
NowI want to report Names and contributions par family tree: My ideal isto list grandfather, father, children based on the two fields (id,parentid).
Any suggestions?
Thanks in advance
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Reporting by family tree
2023-10-05 10:58 Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 13:15 ` Re: Reporting by family tree swastik Gurung <[email protected]>
@ 2023-10-05 14:48 ` Ibrahim Shaame <[email protected]>
2023-10-05 15:02 ` Re: Reporting by family tree David G. Johnston <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Ibrahim Shaame @ 2023-10-05 14:48 UTC (permalink / raw)
To: swastik Gurung <[email protected]>; +Cc: [email protected] <[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
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Reporting by family tree
2023-10-05 10:58 Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 13:15 ` Re: Reporting by family tree swastik Gurung <[email protected]>
2023-10-05 14:48 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
@ 2023-10-05 15:02 ` David G. Johnston <[email protected]>
2023-10-16 11:44 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: David G. Johnston @ 2023-10-05 15:02 UTC (permalink / raw)
To: Ibrahim Shaame <[email protected]>; +Cc: swastik Gurung <[email protected]>; [email protected] <[email protected]>
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Reporting by family tree
2023-10-05 10:58 Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 13:15 ` Re: Reporting by family tree swastik Gurung <[email protected]>
2023-10-05 14:48 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 15:02 ` Re: Reporting by family tree David G. Johnston <[email protected]>
@ 2023-10-16 11:44 ` Ibrahim Shaame <[email protected]>
2023-10-16 13:10 ` Re: Reporting by family tree David G. Johnston <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Ibrahim Shaame @ 2023-10-16 11:44 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: swastik Gurung <[email protected]>; [email protected] <[email protected]>
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.
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Reporting by family tree
2023-10-05 10:58 Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 13:15 ` Re: Reporting by family tree swastik Gurung <[email protected]>
2023-10-05 14:48 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 15:02 ` Re: Reporting by family tree David G. Johnston <[email protected]>
2023-10-16 11:44 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
@ 2023-10-16 13:10 ` David G. Johnston <[email protected]>
2023-10-16 13:29 ` Re: Reporting by family tree swastik Gurung <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: David G. Johnston @ 2023-10-16 13:10 UTC (permalink / raw)
To: Ibrahim Shaame <[email protected]>; +Cc: swastik Gurung <[email protected]>; [email protected] <[email protected]>
On Monday, October 16, 2023, Ibrahim Shaame <[email protected]> wrote:
> 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?
>
That part of the query is outside the CTE and thus doesn’t impact the CTE’s
results. The part of the query that needs to be self-referencing is the
subquery inside the CTE under the Union All.
David J.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Reporting by family tree
2023-10-05 10:58 Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 13:15 ` Re: Reporting by family tree swastik Gurung <[email protected]>
2023-10-05 14:48 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 15:02 ` Re: Reporting by family tree David G. Johnston <[email protected]>
2023-10-16 11:44 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-16 13:10 ` Re: Reporting by family tree David G. Johnston <[email protected]>
@ 2023-10-16 13:29 ` swastik Gurung <[email protected]>
2023-10-17 08:18 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: swastik Gurung @ 2023-10-16 13:29 UTC (permalink / raw)
To: Ibrahim Shaame <[email protected]>; +Cc: [email protected] <[email protected]>
Example Below:
-- create a test family tablecreate table family as(select 1 as id, null::integer as parent_id, 'Grandfather1' as nameunion allselect 2 as id, null::integer as parent_id, 'Grandfather2' as nameunion allselect 3 as id, 1 as parent_id, 'Father1-1' as nameunion allselect 4 as id, 1 as parent_id, 'Father1-2' as nameunion allselect 5 as id, 2 as parent_id, 'Father2-1' as nameunion allselect 6 as id, 3 as parent_id, 'Son1-1-1' as nameunion allselect 7 as id, 4 as parent_id, 'Son1-2-1' as nameunion allselect 8 as id, 5 as parent_id, 'Son2-1-1' as name);
-- create a test contribution tablecreate table contribution as(select 1 as contributor_id, '2020-01-01' as date, 300.00 as contribution_amountunion allselect 1 as contributor_id, '2020-02-01' as date, 255.00 as contribution_amountunion allselect 1 as contributor_id, '2020-03-01' as date, 45.65 as contribution_amountunion allselect 2 as contributor_id, '2020-05-01' as date, 22.55 as contribution_amountunion allselect 2 as contributor_id, '2020-01-01' as date, 450.00 as contribution_amountunion allselect 3 as contributor_id, '2020-02-01' as date, 200.00 as contribution_amountunion allselect 4 as contributor_id, '2020-03-01' as date, 150.00 as contribution_amountunion allselect 4 as contributor_id, '2020-04-01' as date, 60.45 as contribution_amountunion allselect 4 as contributor_id, '2020-05-01' as date, 300.00 as contribution_amountunion allselect 5 as contributor_id, '2020-06-01' as date, 1250.00 as contribution_amountunion allselect 6 as contributor_id, '2020-01-01' as date, 66.50 as contribution_amountunion allselect 7 as contributor_id, '2020-02-01' as date, 855.00 as contribution_amountunion allselect 8 as contributor_id, '2020-02-01' as date, 25.00 as contribution_amount);
-- execute recursive query, all children inheriting contribution sum of parentswith recursive cte as(select f.id, f.parent_id, f.name, c.contribution_amountfrom family fjoin contribution c on f.id = c.contributor_idunion allselect f.id, f.parent_id, f.name, cte.contribution_amountfrom ctejoin family f on cte.id = f.parent_id)select id, name, sum(contribution_amount) as total_contributionfrom ctegroup by id, nameorder by id;
-- execute recursive query, parents have sum of all contributions of its childrenwith recursive cte as(selectf.id,f.parent_id,f.name,c.contribution_amountfromfamily fjoin contribution c onf.id = c.contributor_idunion allselectf.id,f.parent_id,f.name,cte.contribution_amountfromctejoin family f oncte.parent_id = f.id)selectid,name,sum(contribution_amount) as total_contributionfromctegroup byid,nameorder byid;
Change your SQL accordingly. Also, you can add month field to yield results per month.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Reporting by family tree
2023-10-05 10:58 Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 13:15 ` Re: Reporting by family tree swastik Gurung <[email protected]>
2023-10-05 14:48 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 15:02 ` Re: Reporting by family tree David G. Johnston <[email protected]>
2023-10-16 11:44 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-16 13:10 ` Re: Reporting by family tree David G. Johnston <[email protected]>
2023-10-16 13:29 ` Re: Reporting by family tree swastik Gurung <[email protected]>
@ 2023-10-17 08:18 ` Ibrahim Shaame <[email protected]>
2023-10-25 12:20 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Ibrahim Shaame @ 2023-10-17 08:18 UTC (permalink / raw)
To: swastik Gurung <[email protected]>; +Cc: [email protected] <[email protected]>
Thank you David and Swastik, Swastik, I will work on it and will let you
know.
Thanks again for your help
On Mon, Oct 16, 2023 at 4:31 PM swastik Gurung <[email protected]>
wrote:
> Example Below:
>
> -- create a test family table
> create table family as
> (
> select
> 1 as id,
> null::integer as parent_id,
> 'Grandfather1' as name
> union all
> select
> 2 as id,
> null::integer as parent_id,
> 'Grandfather2' as name
> union all
> select
> 3 as id,
> 1 as parent_id,
> 'Father1-1' as name
> union all
> select
> 4 as id,
> 1 as parent_id,
> 'Father1-2' as name
> union all
> select
> 5 as id,
> 2 as parent_id,
> 'Father2-1' as name
> union all
> select
> 6 as id,
> 3 as parent_id,
> 'Son1-1-1' as name
> union all
> select
> 7 as id,
> 4 as parent_id,
> 'Son1-2-1' as name
> union all
> select
> 8 as id,
> 5 as parent_id,
> 'Son2-1-1' as name);
>
> -- create a test contribution table
> create table contribution as
> (
> select
> 1 as contributor_id,
> '2020-01-01' as date,
> 300.00 as contribution_amount
> union all
> select
> 1 as contributor_id,
> '2020-02-01' as date,
> 255.00 as contribution_amount
> union all
> select
> 1 as contributor_id,
> '2020-03-01' as date,
> 45.65 as contribution_amount
> union all
> select
> 2 as contributor_id,
> '2020-05-01' as date,
> 22.55 as contribution_amount
> union all
> select
> 2 as contributor_id,
> '2020-01-01' as date,
> 450.00 as contribution_amount
> union all
> select
> 3 as contributor_id,
> '2020-02-01' as date,
> 200.00 as contribution_amount
> union all
> select
> 4 as contributor_id,
> '2020-03-01' as date,
> 150.00 as contribution_amount
> union all
> select
> 4 as contributor_id,
> '2020-04-01' as date,
> 60.45 as contribution_amount
> union all
> select
> 4 as contributor_id,
> '2020-05-01' as date,
> 300.00 as contribution_amount
> union all
> select
> 5 as contributor_id,
> '2020-06-01' as date,
> 1250.00 as contribution_amount
> union all
> select
> 6 as contributor_id,
> '2020-01-01' as date,
> 66.50 as contribution_amount
> union all
> select
> 7 as contributor_id,
> '2020-02-01' as date,
> 855.00 as contribution_amount
> union all
> select
> 8 as contributor_id,
> '2020-02-01' as date,
> 25.00 as contribution_amount);
>
> -- execute recursive query, all children inheriting contribution sum of
> parents
> with recursive cte as
> (
> select
> f.id,
> f.parent_id,
> f.name,
> c.contribution_amount
> from
> family f
> join contribution c on
> f.id = c.contributor_id
> union all
> select
> f.id,
> f.parent_id,
> f.name,
> cte.contribution_amount
> from
> cte
> join family f on
> cte.id = f.parent_id)
> select
> id,
> name,
> sum(contribution_amount) as total_contribution
> from
> cte
> group by
> id,
> name
> order by
> id;
>
> -- execute recursive query, parents have sum of all contributions of its
> children
> with recursive cte as
> (
> select
> f.id,
> f.parent_id,
> f.name,
> c.contribution_amount
> from
> family f
> join contribution c on
> f.id = c.contributor_id
> union all
> select
> f.id,
> f.parent_id,
> f.name,
> cte.contribution_amount
> from
> cte
> join family f on
> cte.parent_id = f.id)
> select
> id,
> name,
> sum(contribution_amount) as total_contribution
> from
> cte
> group by
> id,
> name
> order by
> id;
>
>
> Change your SQL accordingly. Also, you can add month field to yield
> results per month.
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Reporting by family tree
2023-10-05 10:58 Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 13:15 ` Re: Reporting by family tree swastik Gurung <[email protected]>
2023-10-05 14:48 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 15:02 ` Re: Reporting by family tree David G. Johnston <[email protected]>
2023-10-16 11:44 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-16 13:10 ` Re: Reporting by family tree David G. Johnston <[email protected]>
2023-10-16 13:29 ` Re: Reporting by family tree swastik Gurung <[email protected]>
2023-10-17 08:18 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
@ 2023-10-25 12:20 ` Ibrahim Shaame <[email protected]>
2023-10-25 14:54 ` Re: Reporting by family tree David G. Johnston <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Ibrahim Shaame @ 2023-10-25 12:20 UTC (permalink / raw)
To: swastik Gurung <[email protected]>; +Cc: [email protected] <[email protected]>
Swastik, I have done as you suggested. What I get is:
id | name | total_contribution
----+--------------+--------------------
1 | Grandfather1 | 600.65
2 | Grandfather2 | 472.55
3 | Father1-1 | 800.65
4 | Father1-2 | 1111.10
5 | Father2-1 | 1722.55
6 | Son1-1-1 | 867.15
7 | Son1-2-1 | 1966.10
8 | Son2-1-1 | 1747.55
But what I want to get is grandfather - father - children:
1 - Grandfather1
3 - father1-1
6 - son1-1
7 – son1-2
4 - Father1-2
8 - son2-1
2 – Grandfather2
5 - Father2-1
etc
Any suggestion
Thanks
On Tue, Oct 17, 2023 at 11:18 AM Ibrahim Shaame <[email protected]> wrote:
> Thank you David and Swastik, Swastik, I will work on it and will let you
> know.
> Thanks again for your help
>
> On Mon, Oct 16, 2023 at 4:31 PM swastik Gurung <[email protected]>
> wrote:
>
>> Example Below:
>>
>> -- create a test family table
>> create table family as
>> (
>> select
>> 1 as id,
>> null::integer as parent_id,
>> 'Grandfather1' as name
>> union all
>> select
>> 2 as id,
>> null::integer as parent_id,
>> 'Grandfather2' as name
>> union all
>> select
>> 3 as id,
>> 1 as parent_id,
>> 'Father1-1' as name
>> union all
>> select
>> 4 as id,
>> 1 as parent_id,
>> 'Father1-2' as name
>> union all
>> select
>> 5 as id,
>> 2 as parent_id,
>> 'Father2-1' as name
>> union all
>> select
>> 6 as id,
>> 3 as parent_id,
>> 'Son1-1-1' as name
>> union all
>> select
>> 7 as id,
>> 4 as parent_id,
>> 'Son1-2-1' as name
>> union all
>> select
>> 8 as id,
>> 5 as parent_id,
>> 'Son2-1-1' as name);
>>
>> -- create a test contribution table
>> create table contribution as
>> (
>> select
>> 1 as contributor_id,
>> '2020-01-01' as date,
>> 300.00 as contribution_amount
>> union all
>> select
>> 1 as contributor_id,
>> '2020-02-01' as date,
>> 255.00 as contribution_amount
>> union all
>> select
>> 1 as contributor_id,
>> '2020-03-01' as date,
>> 45.65 as contribution_amount
>> union all
>> select
>> 2 as contributor_id,
>> '2020-05-01' as date,
>> 22.55 as contribution_amount
>> union all
>> select
>> 2 as contributor_id,
>> '2020-01-01' as date,
>> 450.00 as contribution_amount
>> union all
>> select
>> 3 as contributor_id,
>> '2020-02-01' as date,
>> 200.00 as contribution_amount
>> union all
>> select
>> 4 as contributor_id,
>> '2020-03-01' as date,
>> 150.00 as contribution_amount
>> union all
>> select
>> 4 as contributor_id,
>> '2020-04-01' as date,
>> 60.45 as contribution_amount
>> union all
>> select
>> 4 as contributor_id,
>> '2020-05-01' as date,
>> 300.00 as contribution_amount
>> union all
>> select
>> 5 as contributor_id,
>> '2020-06-01' as date,
>> 1250.00 as contribution_amount
>> union all
>> select
>> 6 as contributor_id,
>> '2020-01-01' as date,
>> 66.50 as contribution_amount
>> union all
>> select
>> 7 as contributor_id,
>> '2020-02-01' as date,
>> 855.00 as contribution_amount
>> union all
>> select
>> 8 as contributor_id,
>> '2020-02-01' as date,
>> 25.00 as contribution_amount);
>>
>> -- execute recursive query, all children inheriting contribution sum of
>> parents
>> with recursive cte as
>> (
>> select
>> f.id,
>> f.parent_id,
>> f.name,
>> c.contribution_amount
>> from
>> family f
>> join contribution c on
>> f.id = c.contributor_id
>> union all
>> select
>> f.id,
>> f.parent_id,
>> f.name,
>> cte.contribution_amount
>> from
>> cte
>> join family f on
>> cte.id = f.parent_id)
>> select
>> id,
>> name,
>> sum(contribution_amount) as total_contribution
>> from
>> cte
>> group by
>> id,
>> name
>> order by
>> id;
>>
>> -- execute recursive query, parents have sum of all contributions of its
>> children
>> with recursive cte as
>> (
>> select
>> f.id,
>> f.parent_id,
>> f.name,
>> c.contribution_amount
>> from
>> family f
>> join contribution c on
>> f.id = c.contributor_id
>> union all
>> select
>> f.id,
>> f.parent_id,
>> f.name,
>> cte.contribution_amount
>> from
>> cte
>> join family f on
>> cte.parent_id = f.id)
>> select
>> id,
>> name,
>> sum(contribution_amount) as total_contribution
>> from
>> cte
>> group by
>> id,
>> name
>> order by
>> id;
>>
>>
>> Change your SQL accordingly. Also, you can add month field to yield
>> results per month.
>>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Reporting by family tree
2023-10-05 10:58 Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 13:15 ` Re: Reporting by family tree swastik Gurung <[email protected]>
2023-10-05 14:48 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 15:02 ` Re: Reporting by family tree David G. Johnston <[email protected]>
2023-10-16 11:44 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-16 13:10 ` Re: Reporting by family tree David G. Johnston <[email protected]>
2023-10-16 13:29 ` Re: Reporting by family tree swastik Gurung <[email protected]>
2023-10-17 08:18 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-25 12:20 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
@ 2023-10-25 14:54 ` David G. Johnston <[email protected]>
2023-10-25 16:13 ` Re: Reporting by family tree o1bigtenor <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: David G. Johnston @ 2023-10-25 14:54 UTC (permalink / raw)
To: Ibrahim Shaame <[email protected]>; +Cc: swastik Gurung <[email protected]>; [email protected] <[email protected]>
On Wed, Oct 25, 2023 at 5:21 AM Ibrahim Shaame <[email protected]> wrote:
> But what I want to get is grandfather - father - children:
>
> 1 - Grandfather1
>
> 3 - father1-1
>
> 6 - son1-1
>
> 7 – son1-2
>
> 4 - Father1-2
>
> 8 - son2-1
>
> 2 – Grandfather2
>
> 5 - Father2-1
>
> etc
>
>
> Any suggestion
>
>
If you want a different ordering of the output change the ORDER BY
specification.
Specifically, you want to order by the path of each person. Since that can
only be determined during the traversal you need to create the path data
yourself. I suggest using an integer[] (integer array) to store the path
using ID values as breadcrumbs.
David J.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Reporting by family tree
2023-10-05 10:58 Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 13:15 ` Re: Reporting by family tree swastik Gurung <[email protected]>
2023-10-05 14:48 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 15:02 ` Re: Reporting by family tree David G. Johnston <[email protected]>
2023-10-16 11:44 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-16 13:10 ` Re: Reporting by family tree David G. Johnston <[email protected]>
2023-10-16 13:29 ` Re: Reporting by family tree swastik Gurung <[email protected]>
2023-10-17 08:18 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-25 12:20 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-25 14:54 ` Re: Reporting by family tree David G. Johnston <[email protected]>
@ 2023-10-25 16:13 ` o1bigtenor <[email protected]>
2023-10-25 22:16 ` Re: Reporting by family tree David G. Johnston <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: o1bigtenor @ 2023-10-25 16:13 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Ibrahim Shaame <[email protected]>; swastik Gurung <[email protected]>; [email protected] <[email protected]>
On Wed, Oct 25, 2023 at 9:55 AM David G. Johnston
<[email protected]> wrote:
>
> On Wed, Oct 25, 2023 at 5:21 AM Ibrahim Shaame <[email protected]> wrote:
>>
>> But what I want to get is grandfather - father - children:
>>
>> 1 - Grandfather1
>>
>> 3 - father1-1
>>
>> 6 - son1-1
>>
>> 7 – son1-2
>>
>> 4 - Father1-2
>>
>> 8 - son2-1
>>
>> 2 – Grandfather2
>>
>> 5 - Father2-1
>>
>> etc
>>
>>
>> Any suggestion
>>
>
> If you want a different ordering of the output change the ORDER BY specification.
>
> Specifically, you want to order by the path of each person. Since that can only be determined during the traversal you need to create the path data yourself. I suggest using an integer[] (integer array) to store the path using ID values as breadcrumbs.
>
>
(Not the OP just someone following the list trying to learn.)
'using ID values as breadcrumbs' - - - can I interpret that to mean
the ordinal numbers 1-8 listed (more implied)?
TIA
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Reporting by family tree
2023-10-05 10:58 Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 13:15 ` Re: Reporting by family tree swastik Gurung <[email protected]>
2023-10-05 14:48 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 15:02 ` Re: Reporting by family tree David G. Johnston <[email protected]>
2023-10-16 11:44 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-16 13:10 ` Re: Reporting by family tree David G. Johnston <[email protected]>
2023-10-16 13:29 ` Re: Reporting by family tree swastik Gurung <[email protected]>
2023-10-17 08:18 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-25 12:20 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-25 14:54 ` Re: Reporting by family tree David G. Johnston <[email protected]>
2023-10-25 16:13 ` Re: Reporting by family tree o1bigtenor <[email protected]>
@ 2023-10-25 22:16 ` David G. Johnston <[email protected]>
2023-10-26 11:46 ` Re: Reporting by family tree o1bigtenor <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: David G. Johnston @ 2023-10-25 22:16 UTC (permalink / raw)
To: o1bigtenor <[email protected]>; +Cc: Ibrahim Shaame <[email protected]>; swastik Gurung <[email protected]>; [email protected] <[email protected]>
On Wed, Oct 25, 2023 at 9:13 AM o1bigtenor <[email protected]> wrote:
> On Wed, Oct 25, 2023 at 9:55 AM David G. Johnston
> <[email protected]> wrote:
> >
> > On Wed, Oct 25, 2023 at 5:21 AM Ibrahim Shaame <[email protected]>
> wrote:
> >>
> >> But what I want to get is grandfather - father - children:
> >>
> >> 1 - Grandfather1
> >>
> >> 3 - father1-1
> >>
> >> 6 - son1-1
> >>
> >> 7 – son1-2
> >>
> >> 4 - Father1-2
> >>
> >> 8 - son2-1
> >>
> >> 2 – Grandfather2
> >>
> >> 5 - Father2-1
> >>
> >> etc
> >>
> >>
> >> Any suggestion
> >>
> >
> > If you want a different ordering of the output change the ORDER BY
> specification.
> >
> > Specifically, you want to order by the path of each person. Since that
> can only be determined during the traversal you need to create the path
> data yourself. I suggest using an integer[] (integer array) to store the
> path using ID values as breadcrumbs.
> >
> >
> (Not the OP just someone following the list trying to learn.)
>
> 'using ID values as breadcrumbs' - - - can I interpret that to mean
> the ordinal numbers 1-8 listed (more implied)?
>
>
Yes, the numbers 1-8 are the values assigned to these 8 example individuals
as their unique identifiers.
{1}
{1,3}
{1,3,6}
{1,3,7}
{1,4}
{1,4,8}
{2}
{2,5}
David J.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Reporting by family tree
2023-10-05 10:58 Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 13:15 ` Re: Reporting by family tree swastik Gurung <[email protected]>
2023-10-05 14:48 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 15:02 ` Re: Reporting by family tree David G. Johnston <[email protected]>
2023-10-16 11:44 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-16 13:10 ` Re: Reporting by family tree David G. Johnston <[email protected]>
2023-10-16 13:29 ` Re: Reporting by family tree swastik Gurung <[email protected]>
2023-10-17 08:18 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-25 12:20 ` Re: Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-25 14:54 ` Re: Reporting by family tree David G. Johnston <[email protected]>
2023-10-25 16:13 ` Re: Reporting by family tree o1bigtenor <[email protected]>
2023-10-25 22:16 ` Re: Reporting by family tree David G. Johnston <[email protected]>
@ 2023-10-26 11:46 ` o1bigtenor <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: o1bigtenor @ 2023-10-26 11:46 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Ibrahim Shaame <[email protected]>; swastik Gurung <[email protected]>; [email protected] <[email protected]>
On Wed, Oct 25, 2023 at 5:17 PM David G. Johnston
<[email protected]> wrote:
>
> On Wed, Oct 25, 2023 at 9:13 AM o1bigtenor <[email protected]> wrote:
>>
>> On Wed, Oct 25, 2023 at 9:55 AM David G. Johnston
>> <[email protected]> wrote:
>> >
>> > On Wed, Oct 25, 2023 at 5:21 AM Ibrahim Shaame <[email protected]> wrote:
>> >>
>> >> But what I want to get is grandfather - father - children:
>> >>
>> >> 1 - Grandfather1
>> >>
>> >> 3 - father1-1
>> >>
>> >> 6 - son1-1
>> >>
>> >> 7 – son1-2
>> >>
>> >> 4 - Father1-2
>> >>
>> >> 8 - son2-1
>> >>
>> >> 2 – Grandfather2
>> >>
>> >> 5 - Father2-1
>> >>
>> >> etc
>> >>
>> >>
>> >> Any suggestion
>> >>
>> >
>> > If you want a different ordering of the output change the ORDER BY specification.
>> >
>> > Specifically, you want to order by the path of each person. Since that can only be determined during the traversal you need to create the path data yourself. I suggest using an integer[] (integer array) to store the path using ID values as breadcrumbs.
>> >
>> >
>> (Not the OP just someone following the list trying to learn.)
>>
>> 'using ID values as breadcrumbs' - - - can I interpret that to mean
>> the ordinal numbers 1-8 listed (more implied)?
>>
>
> Yes, the numbers 1-8 are the values assigned to these 8 example individuals as their unique identifiers.
>
> {1}
> {1,3}
> {1,3,6}
> {1,3,7}
> {1,4}
> {1,4,8}
> {2}
> {2,5}
>
Thank you Mr David.
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2023-10-26 11:46 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-05 10:58 Reporting by family tree Ibrahim Shaame <[email protected]>
2023-10-05 13:15 ` swastik Gurung <[email protected]>
2023-10-05 14:48 ` Ibrahim Shaame <[email protected]>
2023-10-05 15:02 ` David G. Johnston <[email protected]>
2023-10-16 11:44 ` Ibrahim Shaame <[email protected]>
2023-10-16 13:10 ` David G. Johnston <[email protected]>
2023-10-16 13:29 ` swastik Gurung <[email protected]>
2023-10-17 08:18 ` Ibrahim Shaame <[email protected]>
2023-10-25 12:20 ` Ibrahim Shaame <[email protected]>
2023-10-25 14:54 ` David G. Johnston <[email protected]>
2023-10-25 16:13 ` o1bigtenor <[email protected]>
2023-10-25 22:16 ` David G. Johnston <[email protected]>
2023-10-26 11:46 ` o1bigtenor <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox