SQL – ROW_NUMBER Analytical Function, Many to One Join

9 12 2009

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

Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 143 other followers

%d bloggers like this: