filtering many-to-many results

From: markdibley <markdibley_at_gmail.com>
Date: Wed, 6 Feb 2008 04:01:51 -0800 (PST)
Message-ID: <8df7f226-f573-476c-b0ed-093552c17f42@i72g2000hsd.googlegroups.com>

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      |

+----------------+

Received on Wed Feb 06 2008 - 06:01:51 CST