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

Matthias Pigulla wrote:
>
> X-No-archive: yes
>
> 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.

A variation on this solution would be a sorted merge: Do two selects:
SELECT whatever you need
 FROM Person
 WHERE personId IN

    (SELECT personId
     FROM Visits)
 ORDER BY name, personId;
SELECT p.name, p.personId, c.whatever you need  FROM Person p JOIN Visits v ON p.personId = v.personId

               JOIN Country c ON v.countryId = c.countryId  ORDER BY p.name, p.personId;

Then step through both resultsets and merge the data. Since both resultsets are sorted by the same data, you can simply advance on both without having to search for a match.

Beware, this only makes sense, if you need a lot of data from the person table. Likewise, if you need a lot of data from the country table, you could use the same technique there.
Doing it for both is not possible with a straight forward merge algorithm! Therefore it should be avoided.

Before I went down this road, I'd check whether the straight forward implementation with one select is really a performance issue.

Regards,
Heinz Received on Wed Sep 19 2001 - 09:03:46 CEST