4 Replies Latest reply on Jun 22, 2010 5:56 AM by LyndonPatton

    Need help with a 3 table join query

    LyndonPatton Level 1

      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