[SQL] grouping a many to many relation set

[Johan Henselmans]

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

smime.p7s

Description: S/MIME cryptographic signature