SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru gavwvin's Avatar
    Join Date
    Nov 2004
    Location
    Cornwall, UK
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Really Complicated Query

    Can anyone help with this, I've got a very complex query to write. I realise it could be done with a combination of php and mysql, but I'd like to try and do it with sql alone, for speed and elegance .

    I need to be able to produce a league table for a racing league, but for teams rather than individuals. I've got the following relevant tables and fields:

    -results, with position and driver id (one row for per driver per race)
    -drivers, with driver id and team id
    -points, with position and number of points (the scoring system)
    -teams, with team name and team id

    it would be a simple

    SELECT *, SUM(points.number_points) as total_points FROM results, drivers, points, teams....GROUP BY teams.team_id ORDER BY total_points

    but heres the twist- if there are more than three drivers in the team only the top three's points count (there may be more than 3 drivers in a team).

    Any suggestions? As I say it doesn't matter too much, as I could use a combination of php and mysql, but I'd prefer a pure mysql solution. I suspect there may be some subqueries, conditionals and LIMIT's involved but I can't see how exactly to fit them together.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if you are anticipating subqueries, does that mean your version of mysql is at least 4.1?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru gavwvin's Avatar
    Join Date
    Nov 2004
    Location
    Cornwall, UK
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah

  4. #4
    SitePoint Guru gavwvin's Avatar
    Join Date
    Nov 2004
    Location
    Cornwall, UK
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    its ok I think I've found one that works... I think I'll print it off and frame it

    SELECT
    SUM(total_points) AS team_points,
    team_id,
    team_name
    FROM (
    SELECT
    drivers.driver_id,
    drivers.driver_name,
    teams.team_name,
    teams.team_id,
    SUM( points.points_points ) AS total_points
    FROM
    drivers,
    results,
    points,
    schedules,
    teams
    WHERE
    drivers.driver_id = results.driver_id AND
    points.result_position = results.result_position AND
    schedules.schedule_id = results.schedule_id AND
    drivers.team_id = teams.team_id AND
    schedules.division_id =1 AND
    teams.team_id = (SELECT team_id FROM teams)
    GROUP BY
    drivers.driver_id
    ORDER BY
    total_points DESC
    LIMIT
    3
    ) AS top_drivers
    GROUP BY
    team_id
    ORDER BY
    team_points

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm really sceptical about that query working properly

    in the very middle, you have

    AND teams.team_id = (SELECT team_id FROM teams)

    this subquery will return more than one row, so the equality will fail

    also, the inner subquery uses LIMIT 3 which limits the number of overall rows to 3, not to 3 for each driver, much less 3 for each team (which is what you want)

    my advice to you is to get the inner query working properly first --
    Code:
    select drivers.driver_id
         , drivers.driver_name
         , teams.team_id
         , teams.team_name
         , sum(points.points_points) as total_points
      from drivers
    inner
      join teams
        on drivers.team_id = teams.team_id
    inner
      join results
        on drivers.driver_id = results.driver_id
    inner
      join schedules   
        on results.schedule_id = schedules.schedule_id
       and schedules.division_id =1
    inner
      join points
        on results.result_position = points.result_position
    group
        by drivers.driver_id
         , drivers.driver_name
         , teams.team_id
         , teams.team_name
    once you have populated your tables with sufficient data that you see teams with more than 3 drivers in these results, then we can move on

    i personally would use a temp or even permanent table to store the results of the above

    it will make what comes later much simpler
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru gavwvin's Avatar
    Join Date
    Nov 2004
    Location
    Cornwall, UK
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, now I think about it theres not really enough data in the database to know that it works for sure... I just tried it and it seemed to work so I got a bit carried away! I'll give your query a go, thanks.


Bookmarks

Posting Permissions

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