How to say it in SQL?

Table a:
RefObjectID, RefClassID
1,1
1,2
1,3
1,4
1,5
1,6
1,7
2,1
2,8
2,9
2,10

Table b:
RefClassID, RefTableID
1,1
1,2
1,3
2,1
2,3
3,1
3,2
3,3
4,1
4,2
4,3
4,4
5,1
5,2
5,3
5,4
6,1
6,2
6,3
7,1
7,2
7,3
8,1
8,10
8,11
9,1
9,10
9,12
10,1
10,11
10,12

What i need is:
I want to know all RefTableID's from b which belong to one RefObjectID in a but only these which are the same for all RefClassID's

when a.RefObjectID=2 then the answer should be 1

when a.RefObjectID=1 then the answer should be 1 and 3

i tried in my Code a Select on table a and than a loop through the RecordSet to bulid another Statement in this form:

Select RefTableID FROM b Where RefClassID=1
Intersect
Select RefTableID FROM b Where RefClassID=8
Intersect
Select RefTableID FROM b Where RefClassID=9
Intersect
Select RefTableID FROM b Where RefClassID=10

But in the real programm the number of Classes from table a which belong to an RefObjectID are about 800-900 and you can imagine how long the String with the statement will be.

Also the trafic on the network is to high because i need the Relation Tables to Object very often.

The Summary of tables which are togeher is also to get easy but this i do not mean:

SELECT DISTINCT(RefTableID) FROM b
WHERE RefClassID IN (SELECT RefClassID FROM a WHERE RefObjectID=2)

Try this:

SELECT A.REFOBJECTID O, B.REFTABLEID T, COUNT(*) C
FROM A, B
WHERE A.REFCLASSID = B.REFCLASSID
AND A.REFOBJID =
GROUP BY A.REFOBJID , B.REFTABLEID
HAVING COUNT(*) = (
SELECT COUNT(*) FROM A
WHERE A.REFOBJID = )
;

This works fine!!!! thx a lot Markus

I tried it in Access and it worked realy fine and fast - i put the Code to the productiveSystem and in ORACLE it does not work - can you explain me what is wrong? i thought this is ANSI-SQL which shoult work for both Databasesystems. Oracle does not throw an Error it only returns an empty recordset - which is wrong.

i have a ZipArchiv which is not big with an MDB which contains the two Tables I use (the names of tables and Colomns difs a bit but it works) is also a Query inside which shows the correct result - i use RefObjectID=53550 for testing - result should be 2,1220,1221 as RefTableID's)

Access-Database

Very strange. I see no problems. Here are my results.

Verbunden mit:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> desc a;
Name Null? Typ
----------------------------------------- -------- ----------------------------
REFOBJECTID NUMBER
REFPKTCLASSID NUMBER

SQL> desc b;
Name Null? Typ
----------------------------------------- -------- ----------------------------
REFPKTCLASSID NUMBER
REFTABLEID NUMBER

SQL> select count(*) from a;

COUNT(*)
----------
530 -- I loaded only the relevant data

SQL> select count(*) from b;

COUNT(*)
----------
6295

SQL> SELECT A.REFOBJECTID O, B.REFTABLEID T, COUNT(*) C
FROM A, B
WHERE A.REFpktCLASSID = B.REFpktCLASSID
AND A.REFOBJectID = 53550
GROUP BY A.REFOBJectID , B.REFTABLEID
HAVING COUNT(*) = (
SELECT COUNT(*) FROM A
WHERE A.REFOBJectID = 53550)
;
2 3 4 5 6 7 8 9
O T C
---------- ---------- ----------
53550 2 530
53550 1220 530
53550 1221 530

SQL>