Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2008
    Posts
    1

    Unanswered: T-SQL query help

    I'm a beginner with T-SQL on SQL Server 2000 so I would appreciate any help anyone can give me. I have a database that tracks horse races. There are currently 2 tables in the database. One table lists all the possible racetracks. The results table holds all the data (results) from each race at each track for each day. The racetrack id is a foreign key in the results table. I built this myself, so I don't know if, referentially speaking, I built it correctly (i.e. followed the normalization rules, etc.). Any race can have any number of horses (usually anywhere between 5 to 16).

    I need to write a query that will return to me the number of races where there were exactly 8 horses in the race.

    The tables are like this (I have not listed all the fields in the Results table because they are not involved in the query I need to make):

    1.) Racetrack table:

    RTID RacetrackName
    ---- --------------
    1 Aqueduct
    2 Arlington Park
    ...


    2) Results table:

    RSID RSDate RTID RSRaceNumber RSHorseNumber RSHorseName
    ---- ------ ---- -------------- -------------- -------------
    1 1/1/08 1 5 1 Filly's Dream
    2 1/1/08 1 5 2 Do Me Over
    3 1/1/08 1 5 3 Rising Star
    4 1/1/08 1 5 4 Blow Me Down
    5 1/1/08 1 5 5 Monkey Shine
    6 1/1/08 1 5 6 Come On Go
    7 1/1/08 1 5 7 Baby Food
    8 1/1/08 1 5 8 Cracker Jax
    9 1/2/08 2 3 1 New In Blue
    10 1/2/08 2 3 2 Old Hat
    11 1/2/08 2 3 3 Fat Dream
    ...

    Can anyone help me with this? Thanks in advance.

  2. #2
    Join Date
    May 2007
    Posts
    49
    Code:
    Select 
    RaceTrack.rtid, 
    RaceTrack.RacetrackName, 
    RaceResult.RSRaceNumber, 
    count(RaceResult.rshousenumber) NumHorses
    from 
    RaceResult
    INNER JOIN RaceTrack ON RaceTrack.rtid = RaceResult.rtid
    group by RaceTrack.rtid, RaceTrack.RacetrackName, RaceResult.RSRaceNumber
    having count(RSRaceNumber) = 8
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    I don't see Cabbage or Toilet Paper in the results list.

    Everybody know that Cabbage is leading by a head and Toilet Paper is wiping up the rear.

    -- This is all just a Figment of my Imagination --

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the number of races where there were exactly 8 horses in the race
    Code:
    SELECT COUNT(*) AS number_of_eight_horse_races
      FROM (
           SELECT RTID, RSRaceNumber 
             FROM RaceResult
           GROUP 
               BY RTID, RSRaceNumber 
           HAVING COUNT(*) = 8
           ) AS eight_horse_races
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Looks like homework, but "What? Me worry?"
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Alfred E. Blindman

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Alfred E. Blindman

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Alfred E. Blindman

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Trying to up your post count?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    post count, pish

    trying to make you smile, is all

    oh, you mean the duplicate posts?

    blame iNET interactive -- it's their stupid b0rked database that did that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That would be MySQL, right? Winky winky winky...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yup, totally

    mysql is the easiest database to use

    and maybe the easiest database to screw up if you don't know what you're doing

    but you can easily screw up other database system applications too, yes?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I try to destroy one thing for every 4 I fix, so that I am always sure to have something to do.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •