Query Help

[Richard Dunnivan]

I have the following tables ...

gameInfo
+------------+
| Field |
+------------+
| gameID |
| year |
| week |
| homeTeamID |
| homeScore |
| awayTeamID |
| awayScore |
+------------+

teamInfo
+----------+
| Field |
+----------+
| teamID |
| coachID |
| teamName |
+----------+

coachInfo
+-----------+
| Field |
+-----------+
| coachID |
| userName |
| coachName |
+-----------+

I'm trying to write a query that will show which coach (or coaches) has won the most number of championships (always week 16)

I have the following that works, but it's very clunky. Is there an easier way to achieve this?


select *
from
(
select coachName, count(*) as championships
from
(
select if(homeScore > awayScore, homeTeamID, awayTeamID) as teamID
from gameInfo
where week = 16
) tab1
left join teamInfo using (teamID)
left join coachInfo using (coachID)
group by coachName
order by championships desc
) tab2
where championships =
(
select max(championships)
from
(
select coachName, count(*) as championships
from
(
select if(homeScore > awayScore, homeTeamID, awayTeamID) as teamID
from gameInfo
where week = 16
) tab3
left join teamInfo using (teamID)
left join coachInfo using (coachID)
group by coachName
order by championships desc
) tab4
)