Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Retrieving many-to-many relationships (maybe off topic/wrong group?)

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

From: Philip Lijnzaad <>
Date: 13 Sep 2001 17:28:25 +0100
Message-ID: <>

> 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 ;-).

Something like

  SELECT p.person_id, p.surname,
  FROM Persons p, Persons_Countries pc, Countries c   WHERE p.person_id =pc.person_id
    AND pc.country_id =
  /* AND p.surname_ucase like '%SMITH%' etc. */   ORDER BY p.person_id, c.country_id;

> When retrieving the data from a RDBMS, would you JOIN the relations, thus
> producing several tuples for each person with one different country each?

yes; I would filter the stuff (i.e. get rid of the recurring person_id's using the person_id). Let the DBMS do as much as possibly for you; depending on your set-up and application, it may even be cheaper to get all the tuples, rather than the 20% you're interested in (the like '%SMITH%' bit), and throw away the 80% locally too.

> 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,

are usually immeasurably cheap, as are CPU cycles

> network)

why 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.

no, the amount of network round trips; they'll be hurting.

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

seems OK to me. Cheers,

Real programs don't eat cache (Malay)
Philip Lijnzaad, \ European Bioinformatics Institute,rm A2-08
+44 (0)1223 49 4639                 / Wellcome Trust Genome Campus, Hinxton
+44 (0)1223 49 4468 (fax)           \ Cambridgeshire CB10 1SD,  GREAT BRITAIN
Received on Thu Sep 13 2001 - 11:28:25 CDT

Original text of this message