Query assistance needed!

[gj6kings]

Hoping to get some assistance to push me in the right direction. I'll do my best to explain my issue and my goal result.

 

I am trying to return data from 3 tables resulting in total yards from two different tables. The third table is a summary table which shares a game_id that all 3 tables reference.

 

I have a game_rushing, game_receiving and game_summary table

 

game_rushing and receiving both have player names, and rushing_yards and receiving_yards and game_id feilds. The sum of rushing and receiving yards by an individual player = all_purpose yards (which is what i am after). In some cases a player may have stats in only 1 field and not the other, while in other cases a player will have stats from both fields in most games but not all. I want to return the yard values from both rushing and recieving whether they are zero or show data.

 

My current query gives me a result only if the player had both yards rushing and receiving in a single game but not if the player had one but not the other in a game..so receivers who dont earn rush yards dont show at all and vice versa..in fact its only showing results of players who earn both rushing and receiving yards in the same games.

 

Here is my current query:

SELECT receiving_player, sum(receiving_yds)as rec_yds, sum(rushing_yds)as rush_yds, game_rushing.team as sqaud,  game_summary.season as season, sum(rushing_yds)+sum(receiving_yds)as all_purpose
FROM `game_summary`,`game_rushing` 
INNER JOIN
game_receiving
on rushing_player=receiving_player
WHERE season='2007' and game_summary.game_id=game_receiving.game_id and
game_summary.game_id=game_rushing.game_id
GROUP BY receiving_player, rushing_player
order by all_purpose desc
LIMIT 10

 

I tried LEFT JOIN, RIGHT JOIN, not using JOIN at all etc, but I always get results from one and not the other..or in this case only results when both fields return data. I need to return all data from recieving yards and rushing yards regardless...I hope this makes sense..sorry if its lengthy...i don't know if anyone can solve this..but im hoping someone can point me in the right direction..

thanks in advance.

[fenway]

You need left joins for each of the tables, with proper join conditions.