SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member mike.greenleaf's Avatar
    Join Date
    Apr 2008
    Location
    St. Paul, MN
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with query involving fantasy football schedule

    My apologies if this question has been asked elsewhere, but I've been searching SitePoint and Google for close to two hours now with no luck...

    I'm working on a site for a fantasy football league, and I'm running into a problem trying to extract the data I need.

    I need to calculate which team scored the most fantasy points for a given game over the lifetime of the league (I'm in the process of updating the league's record book). Sounds simple...

    There is currently a mySQL table named 'schedule' with the following structure:

    id
    week
    season
    away
    home
    awaypts
    homepts

    A typical row looks like this:

    id: 1
    week: 1
    season: 1
    away: 1
    home: 20
    awaypts: 237.2
    homepts: 169.5

    The 'season' field pulls its information from the 'season' table (in this case, 1 stands for 2008) and both 'away' and 'home' pull their information from the 'team' table, which looks like this:

    id
    name
    division

    Obviously, I need to search both 'awaypts' and 'homepts' for the maximum value and return that, along with the team that achieved the feat, their opponent, and the week and season the feat was achieved.

    Is there any way to do something like this using a single query? At the moment I'm using three, which seems unnecessary to me. The first query extracts the highest away scores, the second extracts the highest home scores, and the third finds the opponent for the team in question.

    SELECT awaypts AS Scored, t.name, sc.week, s.year
    FROM schedule sc
    INNER JOIN team t ON t.id = sc.away
    INNER JOIN season s ON s.id = sc.season
    ORDER BY Scored DESC
    LIMIT 5

    SELECT homepts AS Scored, t.name, sc.week, s.year
    FROM schedule sc
    INNER JOIN team t ON t.id = sc.home
    INNER JOIN season s ON s.id = sc.season
    ORDER BY Scored DESC
    LIMIT 5

    SELECT t.name
    FROM schedule sc
    INNER JOIN team t ON t.id = sc.home/away
    WHERE sc.week = '' AND sc.home/away = ''

    As you can see, the third query has to be manually changed once the information is found.

    My main question is: how can I grab the names of both the away and home teams in a single query when they have to be pulled from the same lookup table with the same column name?

    I appreciate any guidance!

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    join the table twice once for home and once for away

  3. #3
    SitePoint Member mike.greenleaf's Avatar
    Join Date
    Apr 2008
    Location
    St. Paul, MN
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Of course - thank you! I knew I was overthinking things...


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
  •