0

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.

user3647071
  • 27
  • 1
  • 6

0 Answers0