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(RefTab leID) FROM b
WHERE RefClassID IN (SELECT RefClassID FROM a WHERE RefObjectID=2)

[Christine]

Re: How to say it in SQL?

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]

Re: How to say it in SQL?

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" <Markus.Strauss @FACOS.dewrote in message
news:d1485ee1.0 311260443.4d375 19e@posting.goo gle.com...

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(RefTab leID) FROM b
WHERE RefClassID IN (SELECT RefClassID FROM a WHERE RefObjectID=2)