I've written a couple queries, but I'm a little unsure of how to combine them to get the results I want. The first query below creates a Top 25 college footbal ranking.
SELECT team, mascot, rankem_team.teamid, record, sum(
CASE WHEN rank =1
THEN rank
ELSE 0
END ) AS firstplace, sum( points ) AS totalpoints
FROM rankem_rank
INNER JOIN rankem_team ON rankem_team.teamid = rankem_rank.teamid
INNER JOIN rankem_record ON rankem_record.teamid = rankem_team.teamid
WHERE rankem_rank.weekid = $week AND
rankem_record.weekid = $week
GROUP BY rankem_team.teamid
ORDER BY totalpoints DESC
LIMIT 25
What I want is to take the results from that Top 25 ranking and use them as the base for the next query that will determine which conference they come from and how many teams are in each conference.
SELECT distinct(conference), count(conference) AS conftotal
FROM rankem_team
INNER JOIN rankem_rank ON rankem_team.teamid = rankem_rank.teamid
WHERE rankem_rank.weekid = $week
GROUP BY conference
ORDER BY conftotal DESC
I've tried to join them together using the IN clause however it gave me an error stating that you couldn't use LIMIT in your version of mySQL. Can anyone think of an alternative way to join these together to get the desired results?
