Richard Huxton wrote:
Johan Henselmans wrote:
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
Thanks for the reply. Adding a group_id column would defeat the whole
purpose of the relational model. I do not want to add a grouping
beforehand. The grouping should take place according to certain
criteria, in this case: group all the records that have at least one of
two attributes in common. I am surprised that I haven't found any
reference to such a n:m grouping, while googling. All I found was a
description of the problem on can get
Johan.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
