X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B66D83A5492 for ; Wed, 1 Dec 2004 05:57:59 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 81906-08 for ; Wed, 1 Dec 2004 05:57:57 +0000 (GMT) Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.250.190.2]) by svr1.postgresql.org (Postfix) with ESMTP id A71D43A54A8 for ; Wed, 1 Dec 2004 05:57:57 +0000 (GMT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 6E28631921; Wed, 1 Dec 2004 06:57:56 +0100 (MET) From: Johan Henselmans X-Newsgroups: pgsql.sql Subject: Re: grouping a many to many relation set Date: Wed, 01 Dec 2004 06:57:54 +0100 Organization: Hub.Org Networking Services Lines: 58 Message-ID: References: <41AC3C7E.10201@archonet.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla Thunderbird 0.9 (Macintosh/20041103) X-Accept-Language: en-us, en In-Reply-To: <41AC3C7E.10201@archonet.com> To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200412/14 X-Sequence-Number: 19634 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.