-
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.
-
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?
-
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.
-
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?
-
a
Last edited by luciffer; 12-17-2003 at 12:19 PM.
-
b
Last edited by luciffer; 12-17-2003 at 12:16 PM.
-
You just write what output you want.
That may help us to write sql.
Tamil
-
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
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|