Re: filtering many-to-many results
Date: Wed, 6 Feb 2008 04:51:04 -0800 (PST)
Message-ID: <1aa7d564-db3c-412a-afc9-9881b63be6be@m34g2000hsb.googlegroups.com>
On Feb 6, 7:01 am, markdibley <markdib..._at_gmail.com> wrote:
> Hello
>
> 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.
>
> My apologises if this is not the right forum to post this in (please
> can you suggest a suitable forum if this is the case, please)
>
> Thanks
>
> mark
>
> 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
Hopefully, the above helps, even if it is not exactly what you wanted.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Wed Feb 06 2008 - 06:51:04 CST