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" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> 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)