469,106 Members | 2,338 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,106 developers. It's quick & easy.

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)
Jul 19 '05 #1
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
Jul 19 '05 #2
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)

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

By using this site, you agree to our Privacy Policy and Terms of Use.