December 9, 2009
A question appeared in the comp.databases.oracle.server Usenet group a couple years ago:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/deeb9d899c8e6376
I have a problem filtering my results which involves a many-to-many join. Below is a brief layout of the problem, but briefly, I have a test that can have multiple results and those results may also belong multiple tests (i have done the same test under a different id and I want to inherit the previous test result). I also have an event table that is required to keep track of all changes.
The problem I have is that I cannot filter my results so where a result belongs to 2 tests I don’t want the details where it belongs to the test in the query. If anyone can suggest a way of returning 2 lines instead of 3 I would be very grateful.
SELECT tr.test_acc, e.event_id, re.resultevent_id, re.result_id, re2.resultevent_id, re2.event_id, tr2.test_acc FROM TestRequest tr JOIN Event e ON tr.test_acc=e.test_acc JOIN ResultEvent re ON e.event_id=re.event_id JOIN ResultEvent re2 ON re.result_id=re2.result_id JOIN Event e2 ON e2.event_id=re2.event_id JOIN TestRequest tr2 ON e2.test_acc=tr2.test_acc WHERE tr.test_acc=3418; +----------+----------+----------------+-----------+----------------+----------+----------+ | test_acc | event_id | resultevent_id | result_id | resultevent_id | event_id | test_acc | +----------+----------+----------------+-----------+----------------+----------+----------+ | 3418 | 42178 | 6345 | 6321 | 6345 | 42178 | 3418 | | 3418 | 42179 | 6346 | 4126 | 4126 | 28004 | 2248 | | 3418 | 42179 | 6346 | 4126 | 6346 | 42179 | 3418 | +----------+----------+----------------+-----------+----------------+----------+----------+ 3 rows in set (0.00 sec)
+-------------+ | TestRequest | +-------------+ | test_acc | +-------------+ 1 | | * | +-------------+ | Event | +-------------+ | test_acc | | event_id | +-------------+ 1 | | * | +----------------+ | ResultEvent | +----------------+ | resultevent_id | | event_id | | result_id | +----------------+
I think that I understand what you are trying to do. I changed the table names slightly (added _) in the mock up:
First, the table creation:
CREATE TABLE TEST_REQUEST ( TEST_ACC NUMBER(10)); CREATE TABLE EVENT ( TEST_ACC NUMBER(10), EVENT_ID NUMBER(10)); CREATE TABLE RESULT_EVENT ( RESULTEVENT_ID NUMBER(10), EVENT_ID NUMBER(10), RESULT_ID NUMBER(10));
As best I can tell, the data that is in the tables:
INSERT INTO TEST_REQUEST VALUES (3418); INSERT INTO TEST_REQUEST VALUES (2248); INSERT INTO EVENT VALUES (3418,42178); INSERT INTO EVENT VALUES (3418,42179); INSERT INTO EVENT VALUES (2248,28004); INSERT INTO RESULT_EVENT VALUES (6345,42178,6321); INSERT INTO RESULT_EVENT VALUES (4126,28004,4126); INSERT INTO RESULT_EVENT VALUES (6346,42179,4126); COMMIT;
I reformatted your query so that I could more easily see what is happening:
SELECT TR.TEST_ACC, E.EVENT_ID, RE.RESULTEVENT_ID, RE.RESULT_ID, RE2.RESULTEVENT_ID, RE2.EVENT_ID, TR2.TEST_ACC FROM TEST_REQUEST TR, EVENT E, RESULT_EVENT RE, RESULT_EVENT RE2, EVENT E2, TEST_REQUEST TR2 WHERE TR.TEST_ACC=3418 AND TR.TEST_ACC=E.TEST_ACC AND E.EVENT_ID=RE.EVENT_ID AND RE.RESULT_ID=RE2.RESULT_ID AND E2.EVENT_ID=RE2.EVENT_ID AND E2.TEST_ACC=TR2.TEST_ACC; TEST_ACC EVENT_ID RESULTEVENT_ID RESULT_ID RESULTEVENT_ID EVENT_ID TEST_ACC ---------- ---------- -------------- ---------- -------------- ---------- ---------- 3418 42179 6346 4126 4126 28004 2248 3418 42179 6346 4126 6346 42179 3418 3418 42178 6345 6321 6345 42178 3418
It appears that if you have 2 rows with the same RESULT_ID, you only want the first EVENT_ID, so you need to have some way of numbering the rows. The ROW_NUMBER analytical function might be able to help:
SELECT TR.TEST_ACC, E.EVENT_ID, RE.RESULTEVENT_ID, RE.RESULT_ID, RE2.RESULTEVENT_ID, RE2.EVENT_ID, TR2.TEST_ACC, ROW_NUMBER() OVER (PARTITION BY RE.RESULT_ID ORDER BY RE2.EVENT_ID) RN FROM TEST_REQUEST TR, EVENT E, RESULT_EVENT RE, RESULT_EVENT RE2, EVENT E2, TEST_REQUEST TR2 WHERE TR.TEST_ACC=3418 AND TR.TEST_ACC=E.TEST_ACC AND E.EVENT_ID=RE.EVENT_ID AND RE.RESULT_ID=RE2.RESULT_ID AND E2.EVENT_ID=RE2.EVENT_ID AND E2.TEST_ACC=TR2.TEST_ACC; TEST_ACC EVENT_ID RESULTEVENT_ID RESULT_ID RESULTEVENT_ID EVENT_ID TEST_ACC RN ---------- ---------- -------------- ---------- -------------- ---------- ---------- -- 3418 42179 6346 4126 4126 28004 2248 1 3418 42179 6346 4126 6346 42179 3418 2 3418 42178 6345 6321 6345 42178 3418 1
Now, if we can filter out any of the rows that do not have RN=1, we may have a usable solution. By sliding the above into an inline view (and adding column aliases as necessary in the inline view), we can add a WHERE clause to return only those rows with RN=1:
SELECT TEST_ACC, EVENT_ID, RESULTEVENT_ID, RESULT_ID, RESULTEVENT_ID2, EVENT_ID2, TEST_ACC2 FROM (SELECT TR.TEST_ACC, E.EVENT_ID, RE.RESULTEVENT_ID, RE.RESULT_ID, RE2.RESULTEVENT_ID RESULTEVENT_ID2, RE2.EVENT_ID EVENT_ID2, TR2.TEST_ACC TEST_ACC2, ROW_NUMBER() OVER (PARTITION BY RE.RESULT_ID ORDER BY RE2.EVENT_ID) RN FROM TEST_REQUEST TR, EVENT E, RESULT_EVENT RE, RESULT_EVENT RE2, EVENT E2, TEST_REQUEST TR2 WHERE TR.TEST_ACC=3418 AND TR.TEST_ACC=E.TEST_ACC AND E.EVENT_ID=RE.EVENT_ID AND RE.RESULT_ID=RE2.RESULT_ID AND E2.EVENT_ID=RE2.EVENT_ID AND E2.TEST_ACC=TR2.TEST_ACC) WHERE RN=1; TEST_ACC EVENT_ID RESULTEVENT_ID RESULT_ID RESULTEVENT_ID2 EVENT_ID2 TEST_ACC2 ---------- ---------- -------------- ---------- --------------- ---------- ---------- 3418 42179 6346 4126 4126 28004 2248 3418 42178 6345 6321 6345 42178 3418
Recent Comments