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 800900 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) 2 1568
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
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 800900 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) This discussion thread is closed Replies have been disabled for this discussion. Similar topics
3 posts
views
Thread by William C. White 
last post: by

2 posts
views
Thread by Albert Ahtenberg 
last post: by

3 posts
views
Thread by James 
last post: by

reply
views
Thread by Ollivier Robert 
last post: by
          