Re: Retrieving many-to-many relationships (maybe off topic/wrong group?)
Date: 13 Sep 2001 17:28:25 +0100
Message-ID: <u766ancaxi.fsf_at_sol6.ebi.ac.uk>
> 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, c.name
FROM Persons p, Persons_Countries pc, Countries c
WHERE p.person_id =pc.person_id
AND pc.country_id = c.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,
Philip
-- Real programs don't eat cache (Malay) ----------------------------------------------------------------------------- Philip Lijnzaad, lijnzaad_at_ebi.ac.uk \ 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 BRITAINReceived on Thu Sep 13 2001 - 18:28:25 CEST