Skip site navigation (1) Skip section navigation (2)

grouping a many to many relation set

From: Johan Henselmans <johan(at)netsense(dot)nl>
To: pgsql-sql(at)postgresql(dot)org
Subject: grouping a many to many relation set
Date: 2004-11-29 20:26:13
Message-ID: EA79F36B-4244-11D9-97F6-000D9328C5BC@netsense.nl (view raw or flat)
Thread:
Lists: pgsql-sql
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




Responses

pgsql-sql by date

Next:From: Michael FuhrDate: 2004-11-29 22:25:50
Subject: Re: stored procedures in postgresql user plpgsql
Previous:From: Chris TraversDate: 2004-11-29 19:13:15
Subject: Re: Way to stop recursion?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group