I am writing a hockey stat database. I am trying to calculate total points for players. total points are a sum from the 1st_assists, 2nd_assists, and goals_for tables. Originally I had the below code working when I had fully populated tables (IE every player had at least one 1st assist). Now that I have just one game, the result set is not including the goals_for info, because the players that have the assists, don't have goals.
I need full outer join capabilities and I have read this isn't available in MySQL. I also read though, one can emulated the full out join by doing a union of a left join and a right join. Given the complexity of this query as it stands already, I am unable to figure out how to do it.
Here is the query:
select p.player_first_name, p.player_last_name, count1, count2, (count1+ifnull(count2,0)) as total_count
from
(select player_id, count(*) count1
from(select * from 1st_assists
union
select * from 2nd_assists) as tem
join players on tem.fk_player_id=players.player_id
group by fk_player_id
order by count(*) desc) q1
left join
(select player_id, count(*) count2
from goals_for
join shots_for on goals_for.fk_shot_for_id=shots_for.shot_for_id
join players on shots_for.fk_player_id=players.player_id
group by player_id) q2
ON q1.player_id=q2.player_id
left join players p ON q1.player_id=p.player_id
order by (count1+count2) desc;
Here are the results:
player_first_name player_last_name count1 count2 total_count
Ray Bourque 1 NULL 1
Dany Heatley 1 NULL 1
Mike Bossy 1 NULL 1
There are 3 other players in this game that scored, which don't appear in the above results. How do a emulate the full outer join that I need? That is what I need, isn't it?
here are the table details and the desired results:
1st_assists:
1st_assist_id
fk_goal_for_id
fk_player_id
2nd_assists:
2nd_assist_id
fk_goal_for_id
fk_player_id
goals_for:
goal_for_id
fk_shot_for_id
game_winner
time
shots_for:
shot_for_id
fk_game_id
fk_player_id
players:
player_id
player_first_name
player_last_name
desired query results
player_first_name player_last_name count1 count2 total_count
Ray Bourque 1 NULL 1
Dany Heatley 1 NULL 1
Mike Bossy 1 NULL 1
Wayne Gretzky NULL 1 1
Paul Coffey NULL 1 1
Bobby Orr NULL 1 1
I am really only interested in the total_count value, which is the sum of 1st_assists, 2nd_assists and goals_for. This query is only based on the first game. There will be many games.