How to say it in SQL?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to say it in SQL?

  1. #1
    Join Date
    Nov 2003
    Location
    Munich/Germany
    Posts
    3

    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)
    Markus Strauss

  2. #2
    Join Date
    Nov 2003
    Posts
    2

    How to say it in SQL

    Try this:

    SELECT A.REFOBJECTID O, B.REFTABLEID T, COUNT(*) C
    FROM A, B
    WHERE A.REFCLASSID = B.REFCLASSID
    AND A.REFOBJID =
    GROUP BY A.REFOBJID , B.REFTABLEID
    HAVING COUNT(*) = (
    SELECT COUNT(*) FROM A
    WHERE A.REFOBJID = )
    ;

  3. #3
    Join Date
    Nov 2003
    Location
    Munich/Germany
    Posts
    3

    Thx a lot

    This works fine!!!! thx a lot Markus
    Markus Strauss

  4. #4
    Join Date
    Nov 2003
    Location
    Munich/Germany
    Posts
    3

    Not so fine

    I tried it in Access and it worked realy fine and fast - i put the Code to the productiveSystem and in ORACLE it does not work - can you explain me what is wrong? i thought this is ANSI-SQL which shoult work for both Databasesystems. Oracle does not throw an Error it only returns an empty recordset - which is wrong.

    i have a ZipArchiv which is not big with an MDB which contains the two Tables I use (the names of tables and Colomns difs a bit but it works) is also a Query inside which shows the correct result - i use RefObjectID=53550 for testing - result should be 2,1220,1221 as RefTableID's)

    Access-Database
    Markus Strauss

  5. #5
    Join Date
    Nov 2003
    Posts
    2

    How to say it in SQL

    Very strange. I see no problems. Here are my results.

    Verbunden mit:
    Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.1.0 - Production

    SQL> desc a;
    Name Null? Typ
    ----------------------------------------- -------- ----------------------------
    REFOBJECTID NUMBER
    REFPKTCLASSID NUMBER

    SQL> desc b;
    Name Null? Typ
    ----------------------------------------- -------- ----------------------------
    REFPKTCLASSID NUMBER
    REFTABLEID NUMBER

    SQL> select count(*) from a;

    COUNT(*)
    ----------
    530 -- I loaded only the relevant data

    SQL> select count(*) from b;

    COUNT(*)
    ----------
    6295

    SQL> SELECT A.REFOBJECTID O, B.REFTABLEID T, COUNT(*) C
    FROM A, B
    WHERE A.REFpktCLASSID = B.REFpktCLASSID
    AND A.REFOBJectID = 53550
    GROUP BY A.REFOBJectID , B.REFTABLEID
    HAVING COUNT(*) = (
    SELECT COUNT(*) FROM A
    WHERE A.REFOBJectID = 53550)
    ;
    2 3 4 5 6 7 8 9
    O T C
    ---------- ---------- ----------
    53550 2 530
    53550 1220 530
    53550 1221 530

    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width