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
...
I think what's missing here is the explicit statement of which group these belong in. Without a value to sort/group by, there's nothing for your queries to "get a grip on".
So - add a "group_id" column to the bank-book and receipt tables. Create a sequence to generate group id's on demand.
Then you'll want a set of triggers that keeps the group details up to date. Of course, groups can shift as you add more records - particularly in the case of two groups merging when you add a "linking" row.
Maybe someone smarter than me can come up with a non-procedural solution. Personally, I've got a nagging feeling that this sort of "connectedness" problem is NP, so scaling could be a problem for you.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
