How to say it in SQL?

[Markus Strauss]

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)

[Christine]

Here's the answer.
select A.RefTableID from
(SELECT RefTableID FROM b
WHERE RefClassID IN
(SELECT RefClassID FROM a WHERE RefObjectID=&1) ) A
group by A.RefTableID
having count(*) = (SELECT count(*) FROM a WHERE RefObjectID=&1)
;
RESULT:
2 -> 1
1 -> 1,3

[VC]

Hello Markus,

If I understand correctly what you want to do, then it's rather simple:

select t1.RefObjectID, t2.RefTableID
from
(select RefObjectID, count(*) cnt
from a
group by RefObjectID
) t1,
(select RefObjectID, RefTableID, count(*) cnt
from a, b
where a.RefClassID=b.RefClassID
group by a.RefObjectID, b.RefTableID
) t2
where t1.RefObjectID=t2.RefObjectID
and t1.cnt=t2.cnt
Rgds.
"Markus Strauss" <Ma************@FACOS.de> wrote in message
news:d1**************************@posting.google.c om...

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)