Normalised beyond my SQL knowledge?

Question

I think I've normalised my database beyond my SQL knowledge. :) Here's a query I'm struggling with from my Snooker league web app. It calculates overall stats for high breaks for a team from all seasons. In the example team id 3.

select max(break_score) maxBreak,avg(break_score) avgBreak,
   count(break_score) breaks
from breaks
join matches on match_id=break_match
where break_player in (
select distinct player_id
from players
join team_members on player=player_id and team=3)
and (match_hometeam=3 or match_awayteam=3)

Tables:

• matches :

match_id int(11) auto_increment
match_hometeam int(11)
match_awayteam int(11)
match_date date
match_void tinyint(4)
match_status tinyint(4)
match_homescore tinyint(4)
match_awayscore tinyint(4)
match_league int(11)

• breaks :

break_id int(11) auto_increment
break_match int(11) foreign key to match_id
break_player int(11)
break_score tinyint(4)
break_clearance tinyint(4)
break_year int(11)

• team_members : many to many table.

team int(11) foreign key to teams table
player int(11) foreign key to players table
year int(11) the year that the player played for the team

The query above works almost as intended apart from one problem. If a player has played for more than one team then Breaks he has for all teams get included in these stats.

If the Breaks table had an extra field 'break_team' the query would be trivial. So my question is two fold, can anyone assist with the right query or should I reduce the normalisation a bit to help with these stats? When is it time to de-normalise?

Answer 1

I don't have a readily available MySQL install, but what you're after is probably the EXISTS clause, try this and reply back if it takes care of what you need or not:

select max(break_score) maxBreak, avg(break_score) avgBreak, count(break_score) breaks
from breaks
join matches on match_id=break_match
where exists(select 1
from players
join team_members on player=player_id and team=3
where break_year = year
and break_player = player_id
and (match_hometeam=3 or match_awayteam=3))