query based on multiple matches
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: query based on multiple matches

  1. #1
    Join Date
    Dec 2003
    Posts
    90

    query based on multiple matches

    hey, i'm new to oracle and i'm trying to write a query that will match data objects based on classifications to a module.

    Data_ID Classification_ID
    1 83
    1 56
    1 102
    2 102
    2 23
    3 4

    Module_ID Classification_ID
    10 83
    10 56
    5 102
    8 102
    8 26
    8 4

    All columns are FK that point to other tables.

    Given the following two tables, is it possible to do a query that will get the data_id from the first table (table A) if and that data_id has at least the same classification_ids as one module_id (Table B).

    EX: module_id 10 has classification_ids 83,56 so data_id 1 would be returned and as well module_id 5 has classification_id 102 so it would be returned, but module_id 8 having classification_ids 102,26,4 has no matching data_id so nothing would be returned for that.

    Any help is appreciated.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    select * from table_A
    where exists
    (select null from table_B
    where table_B.Classification_ID = table_A.Classification_ID);
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Dec 2003
    Posts
    90

    won't work

    this doesn't quite work in this instance.

    This select statement will return all records from table_A where there is at least one record in table_B with the same classification_id.

    I want to return an item from table_A only if one data item (one item = has same data_id) has *all* of the classification_id's as one module item from table B (one module item = has same module_id).

    Table A are data objects that have been classified. Table B are modules that have a classification for the purpose of displaying data objects that meet that classification.

    So, a module could be classified as showing 'html' - 'manuals'
    and a data object could be classified as a 'playstation' - 'html' -'manual'. It would still be returned for this module because it has the requirements of html and manual even though it is also a playstation manual.

    I don't know if this makes any sense or not, but thats the best i can explain it.

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Sorry, I totaly misunderstood your question. Is this what you want?
    Code:
    SELECT a.data_id, b.module_id
      FROM table_a a, table_b b
     WHERE a.classification_id = b.classification_id
      GROUP BY a.data_id, b.module_id
     HAVING COUNT(*) = (SELECT COUNT(*) FROM table_b
                         WHERE module_id=b.module_id);
    If you only want all distinct data_ids that "cover" at least one module, replace the first line with

    SELECT DISTINCT a.data_id FROM ....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Dec 2003
    Posts
    90

    a

    a
    Last edited by luciffer; 12-17-2003 at 11:19 AM.

  6. #6
    Join Date
    Dec 2003
    Posts
    90

    b

    b
    Last edited by luciffer; 12-17-2003 at 11:16 AM.

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    You just write what output you want.
    That may help us to write sql.

    Tamil

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    12:06:17 PTH1>desc t1
    Name Null? Type
    ----------------------------- -------- --------------------
    DATA_ID NUMBER
    CLASSIFICATION_ID NUMBER

    12:06:20 PTH1>desc t2
    Name Null? Type
    ----------------------------- -------- --------------------
    MODULE_ID NUMBER
    CLASSIFICATION_ID NUMBER

    12:06:23 PTH1>select * from t1 ;

    DATA_ID CLASSIFICATION_ID
    ---------- -----------------
    1 83
    1 56
    1 102
    2 102
    2 23
    3 4

    6 rows selected.

    Elapsed: 00:00:00.11
    12:06:34 PTH1>select * from t2 ;

    MODULE_ID CLASSIFICATION_ID
    ---------- -----------------
    10 83
    10 56
    5 102
    8 102
    8 26
    8 4

    6 rows selected.

    Elapsed: 00:00:00.51
    12:06:41 PTH1>
    12:06:41 PTH1>select x1.module_id from
    ( select module_id, count(*) mod_count1 from t2
    group by module_id) X1 ,
    (select module_id, count(*) mod_count2 from t2
    where exists ( select null from t1
    where t1.classification_id = t2.classification_id)
    group by module_id ) X2
    where x1.module_id = x2.module_id and x1.mod_count1 = x2.mod_count2;

    MODULE_ID
    ----------
    5
    10

    Hope this helps.
    Tamil

  9. #9
    Join Date
    Dec 2003
    Posts
    90

    query

    the second query provided by jmodic is what i wanted.

    but what i don't understand (like i said, new to oracle) is why the query works when it is written like this

    SELECT DISTINCT(a.data_id)
    FROM table_a a, table_b b
    WHERE a.classification_id = b.classification_id
    GROUP BY a.data_id, b.module_id
    HAVING COUNT(*) = (SELECT COUNT(*)
    FROM table_b
    WHERE module_id=b.module_id);

    but doesn't work when written like this

    SELECT DISTINCT(table_a.data_id)
    FROM table_a, table_b
    WHERE table_a.classification_id = table_b.classification_id
    GROUP BY table_a.data_id, table_b.module_id
    HAVING COUNT(*) = (SELECT COUNT(*)
    FROM table_b
    WHERE module_id=table_b.module_id);

    what is oracle doing in the background to the query that it works with the table references? Does it do some type of join?

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: query

    Originally posted by luciffer
    but doesn't work when written like this

    SELECT DISTINCT(table_a.data_id)
    FROM table_a, table_b
    WHERE table_a.classification_id = table_b.classification_id
    GROUP BY table_a.data_id, table_b.module_id
    HAVING COUNT(*) = (SELECT COUNT(*)
    FROM table_b
    WHERE module_id=table_b.module_id)
    ;
    That second WHERE clause is causing you troubles. In outer query and in the subquery you use the same table TABLE_B without any aliases, so when resolving the subquery Oracle doesn't know you want to reference the TABLE_B from the outer query. So in effect, oracle resolves the last WHERE clause as:
    Code:
    HAVING COUNT(*) = (SELECT COUNT(*) 
    FROM table_b
    WHERE table_b.module_id=table_b.module_id);
    which of course is always TRUE.

    You must use an alias for the TABLE_B in the outer query in order to reference it in the subquery if you want to join it to the same TABLE_B from the subquery. So you must write it something like this:
    Code:
    SELECT DISTINCT(table_a.data_id)
    FROM table_a, table_b table_b_outer
    WHERE table_a.classification_id = table_b.classification_id 
    GROUP BY table_a.data_id, table_b.module_id
    HAVING COUNT(*) = (SELECT COUNT(*) 
    FROM table_b
    WHERE module_id=table_b_outer.module_id);
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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