Re: Help with select in m-n relation

Clemens Hoffmann wrote:

> Hello, i have two tables (X and Y). Table X containsa kind of grouping
> context and Table Y containsthe contents of the contexts. The contents
> can be indifferent contexts. Therefore the join is done witha m-n
> relation table R. X R Y1 1 - 1 12 1 - 2 23 2
> - 1 3 1 - 4 4 2 - 2 5 3 - 3 6 3 - 4 3
> - 1 3 - 5 3 - 6 In a given situation i have to know what
> records intable Y are refered from some records in table X butonly
> from this records. Lets say records 1 and 2 in table X in the above
> example.The result should be record 2 of table Y Record 1 referes
> 1 2 4Record 2 referes 1 2Record 3 referes 1
> 3 4 5 6 Thus the result should be record 2 as only record 2
> isrefered from only record 1 and 2 in X. Currently it is solved with a
> subselects. SELECT * FROM R WHERE yid IN
> (SELECT DISTINCT yid FROM R WHERE xid IN (1, 2)). This gives us
> all records from the m-n tablewith the record ids in Y which are
> refered from thetwo records in X and additionally the records wherethe
> selected records of X referes to other recordsin Y. Thus we get the
> records (1-1, 1-2, 1-4, 1-2, 2-2,3-1, 3-4). The application then drops
> the records whichhave an other xid as the two initial ids (1 and
> 2). The real tables contain several milion records in tableY and
> serveral tousend records in table Y. The query createsa lot of trafic.
> Sometimes we get 9000 records where only 200are valid. The query
> itself is reasonably fast in Oraclebut i would prefer a better way of
> selecting it. Does anybody has a better and maybe faster
> solution? Greetings Clemens Hoffmann Ps: The application uses
> PosreSQL, too and there the query does notfinish at all (at least not
> in several hours).

Try using EXISTS.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)