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 CEE9F3A51FB for ; Mon, 29 Nov 2004 20:26:44 +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 37894-10 for ; Mon, 29 Nov 2004 20:26:32 +0000 (GMT) Received: from nest.nl (netsense.xs4all.nl [213.84.179.67]) by svr1.postgresql.org (Postfix) with ESMTP id 967F73A51AB for ; Mon, 29 Nov 2004 20:26:23 +0000 (GMT) Received: (qmail 22697 invoked by uid 7819); 29 Nov 2004 20:26:10 -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 0.532013 secs); 29 Nov 2004 20:26:10 -0000 Received: from ppp11.nest.nl (HELO ?193.78.79.51?) (johan@193.78.79.51) by nest.nl with (RC4-SHA encrypted) SMTP; 29 Nov 2004 20:26:05 -0000 Mime-Version: 1.0 (Apple Message framework v619) To: pgsql-sql@postgresql.org Message-Id: Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-10-1046206442; protocol="application/pkcs7-signature" From: Johan Henselmans Subject: grouping a many to many relation set Date: Mon, 29 Nov 2004 21:26:13 +0100 X-Mailer: Apple Mail (2.619) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/292 X-Sequence-Number: 19610 --Apple-Mail-10-1046206442 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed 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 2156 | 573 2156 | 574 2156 | 575 1710 | 575 1710 | 576 I have already grouped them according to the way they should be grouped: bankbook payments and receipt amounts that are part of the same transaction (they are a subset of a large set of payments and receipts, most 1-1, 1-n and n-1, which are solved relatively easy). As you can see there are a few records that interconnect the payments and receipts: bankbookdetid | receiptid ---------------+----------- 157 | 25 332 | 100 2156 | 575 1710 | 575 I tried now for some time how a SQL statement could give a set grouped as you can see above, but I just don't seem to see it. Is there anyone around over here that had a similar situation and has found a solution? Should I try to do this in PL/SQL? Is there a solution for the problem anyway? -johan --Apple-Mail-10-1046206442 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 DxcNMDQxMTI5MjAyNjE0WjAjBgkqhkiG9w0BCQQxFgQU6jqBQY+8Swl6rjFtnddLfn07FeIwgZEG CSsGAQQBgjcQBDGBgzCBgDB5MRAwDgYDVQQKEwdSb290IENBMR4wHAYDVQQLExVodHRwOi8vd3d3 LmNhY2VydC5vcmcxIjAgBgNVBAMTGUNBIENlcnQgU2lnbmluZyBBdXRob3JpdHkxITAfBgkqhkiG 9w0BCQEWEnN1cHBvcnRAY2FjZXJ0Lm9yZwIDALFZMIGTBgsqhkiG9w0BCRACCzGBg6CBgDB5MRAw DgYDVQQKEwdSb290IENBMR4wHAYDVQQLExVodHRwOi8vd3d3LmNhY2VydC5vcmcxIjAgBgNVBAMT GUNBIENlcnQgU2lnbmluZyBBdXRob3JpdHkxITAfBgkqhkiG9w0BCQEWEnN1cHBvcnRAY2FjZXJ0 Lm9yZwIDALFZMA0GCSqGSIb3DQEBAQUABIIBAGz4zMvdap3UextZlcrR2FuQq2iZzVkA//nV+8Su CJMlIE0UISYNVr8VHsltG1XdDwxIMmD/6chgdRaLYwRZKSEUsI0HI391Xzah6Xdzp4KSmYabbTFH 4BHZrNkDc43bmHNjMaSxbgm3HEmGI6qJ3rqb5N8yXK5Q1MQO04i4Di0lfQih07+VbzE8pbN/cwbe 6VtrD6LaBMOW8hO1aiHVbSIHg7Z3uiEujQHDpku85wPyyidQgjeAxXOck2/jYBazsTpoWhgO97K3 GjY35wx+eX72xQHDaI86qq4Ml+N0AbTS1qpD8Z7BQlqwbKOAm27AZLhJoSj0FxFGqVqY+4ZgkMMA AAAAAAA= --Apple-Mail-10-1046206442--