Need help with a 3 table join query

[LyndonPatton]

I'm writing reports from data that comes from a 3rd party subscription. The data has not been normalized as you will read.

Requirement: Rank top 3 Dams by the money earned by their foals. List DamID, DamName, and MoneyEarnedByHerFoals (SumOfAmountWon).

A "Foal" is the horse that races, the "Dam" is that race horses mother. The Dam owner will be compensated based on the performance of her foals. A Dam may have many foals active in a year. A foal may earn money many times in a year.

Tables:

Races: Horse race results for calendar year. One record for each performance that earned money.

OBN (FoalID)     Amount (Foal Earnings) RaceDate

10                      500                              02/02/2010

20                      1000                            02/02/2010

20                      1000                            03/02/2010

30                      500                              03/02/2010

40                      500                              04/02/2010

50                      1000                            04/02/2010

Racestck: Foal details. This table is a problem. There are often more that one record per foal in this table as one is added when ownership changes. I have no control over this and need a Distinct view of this table.

OBN (FoalID) DamID Breed Owner

10                 100      TB      Joe

10                 100      TB      Mary

20                 200      TB      Sue

30                 100      TB      Mike

40                 200      TB      Joe

50                 100      TB      Joe

DamSire: Dam Details. This table is a problem. There are often more that one record per Dam in this table as one is added when ownership changes. I have no control over this and need a Distinct view of this table.

OBN (DamID) HName (HorseName) Owner

100                BigMomma                Smith

200                TrackMom                 Jones

100                BigMomma                Green

Query:

This is the query so far. It applies the Distinct keyword to the Dam Table and the results are accurate if there are no duplicate OBN's in the Racestck table. I've tried to get a "distinct" view of the racestck table as well but I can't figure it out. Everything I try applies the distinct to the SumOfAmountWon as well and that does no good...

<cfquery name="getWinningDams" datasource="#SelectedFolder#">
  SELECT TOP 3 D.OBN, Sum(R.Amount) AS SumOfAmountWon, D.HName
  FROM (Racestck F INNER JOIN Races R ON F.OBN = R.OBN)
  INNER JOIN (SELECT DISTINCT OBN, HName FROM DamSire
  ) D ON F.DamOBN = D.OBN
  Where F.Breed='TB'
  GROUP BY D.OBN, D.HName
  ORDER BY 2 DESC
</cfquery>

Thank you for taking the time to read...

Lyndon

[Dan_Bracuk]

In the problem tables, are there no fields that might help you identify the current owner?  A date field would help. 

[LyndonPatton]

There is a column "OWNSSNEXT" (Owner SSN Extension) that is an incrementing alpha. Original Owner is "A" fourth owner is "D". So I guess Max(OWNSSNEXT) is the current Owner.

Lyndon

[Dan_Bracuk]

You put that max(ownsnext) in a subquery.

select etc

from table t join

(select field, max(ownsnext) theowner

group by field) sq on t.field = sq.field and t.ownsnext= theowner

etc

You have to give your subquery an alias when you do this.

[LyndonPatton]

I have a sloution. Thanks Dan.

SELECT TOP 3 D.OBN, Sum(R.Amount) AS SumOfAmountWon, D.HName
FROM (Racestck F INNER JOIN Races R ON F.OBN = R.OBN)
INNER JOIN (SELECT DISTINCT OBN, HName FROM DamSire
) D ON F.DamOBN = D.OBN
Where F.Breed='TB'
And UCase(F.OWNSSNEXT)<'B'
GROUP BY D.OBN, D.HName
ORDER BY 2 DESC