Multiple Joins

[ninedoors]

I am pulling out stats for a hockey league I run.  What I would like to do it is to be able to pull all the data I need out of the multiple tables in one query without using sub queries.

 

I have a table that holds all of the players in my hockey league.  It holds the id number, team id and jersey number.  I have a scoring table the has a line for each goal of assist that is scored with the player id and I have a games played table which has a row for each player that played in a given game.

 

I am able to get the data out right now with one query but I have to use sub queries to do it.  My query right now looks like this:

 

'
    SELECT s.id_member, s.team_id, sm.first_name, sm.last_name, t.alt_name, mt.jersey_id, d.division,
    SUM(IF(s.action_id = 1, 1, 0)) as g, 
    SUM(IF(s.action_id = 2, 1, 0)) as a, 
    SUM(IF(s.action_id = 2 OR s.action_id = 1, 1, 0)) as pts,
    SUM(IF(s.action_id = 1 AND s.gwg = 1, 1, 0)) as gwg, 
    SUM(IF(s.action_id = 1 AND s.advantage > 0, 1, 0)) as ppg, 
    SUM(IF(s.action_id = 1 AND s.advantage < 0, 1, 0)) as shg, 
    ('.$pens_query.') as pims,
    ('.$games_query.') as gp,
    (SUM(IF(s.action_id = 2 OR s.action_id = 1, 1, 0))/('.$games_query.')) as pg
    FROM {db_prefix}osm_member_teams as mt
    LEFT JOIN {db_prefix}members as sm ON(mt.id_member = sm.id_member)
    LEFT JOIN {db_prefix}osm_scoring as s ON(mt.id_member = s.id_member AND mt.team_id = s.team_id)
    LEFT JOIN {db_prefix}osm_teams as t ON(mt.team_id = t.team_id) 
    LEFT JOIN {db_prefix}osm_schedule as sc ON(s.game_id = sc.game_id) 
    LEFT JOIN {db_prefix}osm_divisions as d ON(t.division = d.division_id)    
    WHERE mt.position != {string:pos} AND mt.rostered = 1 AND sc.finished != 3'

 

This works but the problem is that if a player hasn't recorded a point he won't show up in this query because he isn't in the scoring table(osm_scoring).  What I need is that if any player has played a game then they show up in the query.  Any help would be great.

 

The games_query is a sub query to pull out the number of games played from that table.  Same with the pims_query.

 

Nick