Re: filtering many-to-many results

On 6 Feb, 12:51, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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.

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 Received on Wed Feb 06 2008 - 07:15:09 CST