Help with select in m-n relation

Hello,

i have two tables (X and Y). Table X contains a kind of grouping context and Table Y contains the contents of the contexts. The contents can be in different contexts. Therefore the join is done with a m-n relation table R.

X R Y

1    1 - 1    1
2    1 - 2    2    
3    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 in table Y are refered from some records in table X but only 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 4
Record 2 referes 1 2
Record 3 referes 1 3 4 5 6

Thus the result should be record 2 as only record 2 is refered 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 table with the record ids in Y which are refered from the two records in X and additionally the records where the selected records of X referes to other records in 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 which have an other xid as the two initial ids (1 and 2).

The real tables contain several milion records in table Y and serveral tousend records in table Y. The query creates a lot of trafic. Sometimes we get 9000 records where only 200 are valid. The query itself is reasonably fast in Oracle but 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 not finish at all (at least not in several hours). Received on Thu Jun 12 2003 - 05:54:13 CDT