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 130083A5A40 for ; Fri, 3 Dec 2004 20:03:12 +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 06352-08 for ; Fri, 3 Dec 2004 20:02:54 +0000 (GMT) Received: from nest.nl (netsense.xs4all.nl [213.84.179.67]) by svr1.postgresql.org (Postfix) with ESMTP id 5EE4D3A3EA3 for ; Fri, 3 Dec 2004 20:02:39 +0000 (GMT) Received: (qmail 3678 invoked by uid 7819); 3 Dec 2004 20:02:27 -0000 Received: from 193.78.79.51 by eagle.nest.nl (envelope-from , uid 7791) with qmail-scanner-1.24 (clamdscan: 0.80/561. Clear:RC:1(193.78.79.51):. Processed in 1.361039 secs); 03 Dec 2004 20:02:27 -0000 Received: from ppp11.nest.nl (HELO ?193.78.79.51?) (johan@193.78.79.51) by nest.nl with (RC4-SHA encrypted) SMTP; 3 Dec 2004 20:02:20 -0000 In-Reply-To: <20041202024556.GA40090@winnie.fuhr.org> References: <41AC3C7E.10201@archonet.com> <20041202024556.GA40090@winnie.fuhr.org> Mime-Version: 1.0 (Apple Message framework v619) Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-1--757105185; protocol="application/pkcs7-signature" Message-Id: <40FC4EB4-4566-11D9-8326-000D9328C5BC@netsense.nl> Cc: pgsql-sql@postgresql.org From: Johan Henselmans Subject: Re: grouping a many to many relation set Date: Fri, 3 Dec 2004 21:02:21 +0100 To: Michael Fuhr X-Mailer: Apple Mail (2.619) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200412/31 X-Sequence-Number: 19651 --Apple-Mail-1--757105185 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed On 2-dec-04, at 3:45, Michael Fuhr wrote: > On Wed, Dec 01, 2004 at 06:57:54AM +0100, Johan Henselmans wrote: >> Richard Huxton wrote: >> >>> 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. >> >> 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. > > How is an application going to know which records belong to which > groups without a group ID? Or is a group ID acceptable as long as > it's not part of the data, but rather generated by the query or > function that does the grouping? > >> 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. > > What about chains like this: > > bankbookdetid | receiptid > ---------------+----------- > 100 | 1 > 100 | 2 > 101 | 2 > 101 | 3 > 102 | 3 > 102 | 4 > > Should 1 be grouped with 2, 3, and 4 since 1 has an attribute in > common with 2, 2 has an attribute in common with 3, and 3 has an > attribute in common with 4? Or doesn't your model permit this > situation? > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > That is possible indeed. This was the original situation. The trick I use is that from that collection I select the records of which both attribues have a count > 1. But that leads to the situation that some attributes still appear several times in the resulting set. In the above case the resulting set would be 100 | 2 101 | 2 101 | 3 102 | 3 So the group would end up to be all the records that have 2 and 3 or 100, 101 and 102 as part. In the mean time, breaking my head about it, I think I have found a solution: from the resuling set I once again select the record attributes that have a common attribute count > 1 (In your example that would be bankbookdetid = 101, and receiptid 2 and 3). Then we reduce the resulting set to: 101 | 2 101 | 3 Now I can add a group id , which is the attribute of which the count is > 1 (in this case 101), to make sure we don't mix up groupid because bankbookdetid and receiptid might have the same number, so we add 3000000 to receiptid groups, and 400000000 to bankbookdetid groups. That is the final set, from where one could go back to the previous set (get all the records that have 101 or 2 and 3 in their respective attributes), that would result in 100 | 2 | 400000101 101 | 2 | 400000101 101 | 3 | 400000101 102 | 3 | 400000101 and from there one would go back to ask all the records that have a part in these records, so you could go back to ---------------+----------- 100 | 1 | 400000101 100 | 2 | 400000101 101 | 2 | 400000101 101 | 3 | 400000101 102 | 3 | 400000101 102 | 4 | 400000101 Now it is possible to group. What do you think of the idea? -johan --Apple-Mail-1--757105185 Content-Transfer-Encoding: base64 Content-Type: application/pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIE0TCCBM0w ggK1oAMCAQICAwCxWTANBgkqhkiG9w0BAQQFADB5MRAwDgYDVQQKEwdSb290IENBMR4wHAYDVQQL ExVodHRwOi8vd3d3LmNhY2VydC5vcmcxIjAgBgNVBAMTGUNBIENlcnQgU2lnbmluZyBBdXRob3Jp dHkxITAfBgkqhkiG9w0BCQEWEnN1cHBvcnRAY2FjZXJ0Lm9yZzAeFw0wNDA5MzAxNTU3MDJaFw0w NTA5MzAxNTU3MDJaME8xKzApBgNVBAMTIkpvaGFubmVzIFBldHJ1cyBDbGVtZW5zIEhlbnNlbG1h bnMxIDAeBgkqhkiG9w0BCQEWEWpvaGFuQG5ldHNlbnNlLm5sMIIBIjANBgkqhkiG9w0BAQEFAAOC AQ8AMIIBCgKCAQEAsluwUpkQ/3m2xfGf2JX7pQ3BczFqvBmpfKTc7FrNKwnfXZsJxra88LStzYN6 WpaV9qT0xT9KzR5adxD2B/+4SxIsq8xk0gvBWOHz2dTQPJjlyEinitFBvsprhEgYBHCX8pf/p3ZS 5XbYKZMmxy+h7u/lEFFquitXJ/S0giPY0jAfhF5JiCOp7ldlJo3euQZhodIA6+d/ojx4CS5N0Fi6 2kpTN6i86QcA2kJWq1DHpjqzdzwHDVw4Nvey/FU/96DjIIxO8EYErE5wF/RqJo3EJHGkphF+TMDz GJ+GT2kyaVzHFBwviThphY3YNzWbT8xGYudLUiLVVR+LcjKB/KL+8wIDAQABo4GHMIGEMAwGA1Ud EwEB/wQCMAAwVgYJYIZIAYb4QgENBEkWR1RvIGdldCB5b3VyIG93biBjZXJ0aWZpY2F0ZSBmb3Ig RlJFRSBoZWFkIG92ZXIgdG8gaHR0cDovL3d3dy5jYWNlcnQub3JnMBwGA1UdEQQVMBOBEWpvaGFu QG5ldHNlbnNlLm5sMA0GCSqGSIb3DQEBBAUAA4ICAQCvWeKIeQY2EcZW3u1YYUmRmSHYSmilOfGL mZE+hXZzlzmGNA3TpLZUn0X7Oto1NBUSGfEpbWoA0IOhr2NBx8ERJhQ5/sbC5zfl7Eaf1qzeYik3 oSfFqBuu8Gd9gsftl2fUNJYRpzyJAhd3jopGxZRDjvNbxGv0VLOoPTrGdohQ1meIzItiu1QpfV0U tQ4z5ScuCXgPP37wSqNGAR8fjjlObusBxc3OkM90/aE7kYB1o1v1G5bCgbN3/IH4QzjRQKmsexQq Dql5spyW47Y/7nmNCdh/mrnxMpKR1pqJX62APyndatlB4sN9lPj7pA2+OUS9qPyCLa2ey7XQRn34 GCoT7SypOwKuvtwuXxwPDFIJt1j0jJYa9YjlVvrDSUCO/iDeImuiEIdLYfwymyZQp4ryrbNzLgUP E2so2UwtThg7dfZfMq2TAhOzN7TPRVnoUvMvw8vZin8vcLd+MnhTuNQqJTaiABEK9jr4Uw48BQB7 3vN/PM08qgsFOo6fqfU9VdqC97DrI17aMn8aUIiceE8beGKw0MbQeFPBo812+1Z1v/oGH0JjuSM+ 0qZOE6QOFWYp0siDA+tZVK8Ua3KD3QDfMf8Lyn+68AJQzgPCve37qeGebq9uqeEPxV+asCsi4nS7 O638ksEmG9w45a4pNMTC9b7/03uHmhwNedzojHOlQzGCAzMwggMvAgEBMIGAMHkxEDAOBgNVBAoT B1Jvb3QgQ0ExHjAcBgNVBAsTFWh0dHA6Ly93d3cuY2FjZXJ0Lm9yZzEiMCAGA1UEAxMZQ0EgQ2Vy dCBTaWduaW5nIEF1dGhvcml0eTEhMB8GCSqGSIb3DQEJARYSc3VwcG9ydEBjYWNlcnQub3JnAgMA sVkwCQYFKw4DAhoFAKCCAYcwGAYJKoZIhvcNAQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUx DxcNMDQxMjAzMjAwMjIxWjAjBgkqhkiG9w0BCQQxFgQUb2dBw+5bHdNIIQfKoWmIIOJ5T38wgZEG CSsGAQQBgjcQBDGBgzCBgDB5MRAwDgYDVQQKEwdSb290IENBMR4wHAYDVQQLExVodHRwOi8vd3d3 LmNhY2VydC5vcmcxIjAgBgNVBAMTGUNBIENlcnQgU2lnbmluZyBBdXRob3JpdHkxITAfBgkqhkiG 9w0BCQEWEnN1cHBvcnRAY2FjZXJ0Lm9yZwIDALFZMIGTBgsqhkiG9w0BCRACCzGBg6CBgDB5MRAw DgYDVQQKEwdSb290IENBMR4wHAYDVQQLExVodHRwOi8vd3d3LmNhY2VydC5vcmcxIjAgBgNVBAMT GUNBIENlcnQgU2lnbmluZyBBdXRob3JpdHkxITAfBgkqhkiG9w0BCQEWEnN1cHBvcnRAY2FjZXJ0 Lm9yZwIDALFZMA0GCSqGSIb3DQEBAQUABIIBADa4vHdcDZ2OMD8qICklpN16Biuu6bBpo9YYEPXH sXB/3fkWtfNH/1v9ZnSFiOzQfOiZ4qXgIJfXmRvEvAoQJsskIguQdZEfAY3Aj3ZeKY6xgl1EQ0iz uvQPSD2t2u+SAop8cQ4ZrWH7SdoYdvBcEz4QAa5Hdo1nHKc2t2V6EdNzVnFyCy57QetM+8m6mu1H Z8Omjj7oSg7FTW4npTESfOocHmxAby+QyVkKCGsL1wZwfDtCIrlfvkU2UdUrJAQfOGQvjW/Xgknh 1HFp4pfu5Yt5aap74M5l4GO/Yp1VpwLq81GkqtbVLSMVnke8eQQpFmok30zHSE9b5B6NE2fgEOEA AAAAAAA= --Apple-Mail-1--757105185--