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!