Re: filtering many-to-many results

"markdibley" <markdibley_at_gmail.com> schreef in bericht news:8df7f226-f573-476c-b0ed-093552c17f42_at_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 |
> +----------------+

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 Received on Wed Feb 06 2008 - 06:26:37 CST