filtering many-to-many results
markdibley
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 |
+----------------+
shakespeare
"markdibley" <markd...@gmail.com> schreef in bericht
news:8df7f226-f573-476c...@i72g2000hsd.googlegroups.com...
Since all three rows have different values, you should decide in some way
which row you want to see... If the difference in values is not relevant, do
not include them in your select clause and use distinct.
Shakespeare
Charles Hooper
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.
markdibley
Dear Charles
That is a great solution and makes perfect sense.
However, unfortunately, having read back through my original post I
realise that I must have deleted something that accompanied the "if
this is the wrong forum" etc. because I am actually using MySQL (yes,
I know, sorry). I was hoping that my problem was just a "basic" sql or
db design problem rather than a limitation with the dbms.
My apologises for this omission, but thank you again for the solution.
Mark