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.