Retrieving many-to-many relationships (maybe off topic/wrong group?)

Hello folks,

I've got a little question concerning the processing of a many-to-many relationship. I will use a concrete example for my problem, although I came across this issue quite often; now I wonder how professionals like YOU would do it :-)

Basically, I've got a 'person' relation, a 'country' relation and a 'visits' relation providing a many-to-many relation between person and country.

I'd like to print a list of all (or some, no matter) persons, and for each of them a comma-separated enumeration of all countries they visit. Clearly this is an application layer issue, however, I hope you don't mind ;-).

When retrieving the data from a RDBMS, would you JOIN the relations, thus producing several tuples for each person with one different country each? By sorting appropriately (e. g.on person's PK) and adding some lines of code, one could achieve the goal. However, there's some overhead (memory, network) for the data that is processed several times.

On the other hand, one could first select all persons and then query for each one's countries successively. The disadvantage is a higher amount of statements to process.

(If this is the wrong newsgroup, could you please point me to another one?)

TIA,
Matthias Received on Thu Sep 13 2001 - 16:32:05 CEST