From: Johan Henselmans <johan@netsense.nl>
To: pgsql-sql@postgresql.org
Subject: grouping a many to many relation set
Date: Mon, 29 Nov 2004 21:26:13 +0100
Message-ID: <EA79F36B-4244-11D9-97F6-000D9328C5BC@netsense.nl> (raw)
Hi, I am having a problem grouping a many to many relationship with
payments and receipts, where a payment can be for multiple receipts,
and a receipt can have multiple payments. I got a list of records that
are involved in such relations, but now I don't know how to group them
so that all payments and rececipts belonging to the same group are
properly grouped. Here's the list:
bankbookdetid | receiptid
---------------+-----------
147 | 25
157 | 25
157 | 622
321 | 100
332 | 101
332 | 100
2156 | 573
2156 | 574
2156 | 575
1710 | 575
1710 | 576
I have already grouped them according to the way they should be
grouped: bankbook payments and receipt amounts that are part of the
same transaction (they are a subset of a large set of payments and
receipts, most 1-1, 1-n and n-1, which are solved relatively easy).
As you can see there are a few records that interconnect the payments
and receipts:
bankbookdetid | receiptid
---------------+-----------
157 | 25
332 | 100
2156 | 575
1710 | 575
I tried now for some time how a SQL statement could give a set grouped
as you can see above, but I just don't seem to see it. Is there anyone
around over here that had a similar situation and has found a solution?
Should I try to do this in PL/SQL? Is there a solution for the problem
anyway?
-johan
Attachments:
smime.p7s (application/pkcs7-signature, 2.1K) download
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: pgsql-sql@postgresql.org
Cc: johan@netsense.nl
Subject: Re: grouping a many to many relation set
In-Reply-To: <EA79F36B-4244-11D9-97F6-000D9328C5BC@netsense.nl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox