LEFT JOIN issue

chuckball

I've been racking my brain to no avail trying to get this query to work out...probably something simple but here it is...

I've essentially got the following query

SELECT nbag.,ft.,fl., p., s.*
FROM
fantasy_teams as ft,nba_players AS p,fantasy_lineups AS fl,
nba_games as nbag LEFT JOIN nba_stats AS s
ON ((s.tm = nbag.home_team OR s.tm = nbag.away_team) AND
s.game_date = nbag.game_date)
WHERE
fl.player_id = s.player_id AND
p.player_id = fl.player_id AND
ft.team_id = fl.team_id AND
week = '2003-10-27' AND
nbag.game_date >= '2003-10-27' AND
nbag.game_date < '2003-11-03'
ORDER BY fl.team_id, fl.status desc,p.player_id,nbag.game_date

The key tables having to do with the LEFT JOIN are the games and stats tables. The stats table only has records for games that a player actually played in. The two tables in question have the following format.

nba_games
pkey
game_date
home_team
away_team

nba_stats
pkey
game_date
tm
player_id
multiple stat fields...

So basically I'm trying to set up one query that grabs all the games played in a particular week including game records that are not in the stats table (ie a player may have been injured).

I beleive the problem is stemming from the fact that the tm field in the stats table can be one of two fields in the games table, but I'm not sure.

It's probably just too late right now and I'm missing something simple but for the life of me it's eluding my consciousness.

Can anybody help out here.

Thanks in advance.

ultraslacker

The error is in the first expression of the inner joins - fl.player_id = s.player_id. You have to join on s.player_id, but it needs to be in the outer join.

Is there a need to have tm in nba_stats? It would be cleaner to just use player_id. Also use nba_games_pkey instead of nba_stats.game_date.

chuckball

Thanks ultraslacker,

I've written it like this...

SELECT nbag. , ft. , fl. , p. , s. *
FROM fantasy_teams AS ft, nba_players AS p, fantasy_lineups AS fl
LEFT JOIN (
nba_games AS nbag
LEFT JOIN nba_stats AS s ON ( (
s.tm = nbag.home_team OR s.tm = nbag.away_team
) AND s.game_date = nbag.game_date )
) ON ( fl.player_id = s.player_id )
WHERE (
p.tm = nbag.home_team OR p.tm = nbag.away_team OR s.tm = nbag.home_team OR s.tm = nbag.away_team
) AND p.player_id = fl.player_id AND ft.team_id = fl.team_id AND week = '2003-10-27' AND nbag.game_date >= '2003-10-27' AND nbag.game_date < '2003-11-03'
ORDER BY fl.team_id, fl.status DESC , p.player_id, nbag.game_date

This seems to work, however because of the reasons below it would be either impossible or impractical to implement your other suggestions, can you see any other way to speed the query up at all?

I can't put the tm in the player table since players sometimes change teams, so I have to know for each stat record (individual game) what tm that player played on.

I know it would have been cleaner to use a pkey to join the stats and games table but unfortuneately I received the initial stats data without that so I have been continuing the same structure this year as well, so now I've got 30,000 records all with the following structure.

If not I can live with this functioning, thanks again for you help!

ultraslacker

SELECT * 
FROM fantasy_teams AS ft, 
nba_players AS p, 
fantasy_lineups AS fl,
nba_games AS nbag 
LEFT JOIN nba_stats AS s ON 
(s.game_date = nbag.game_date
 AND fl.player_id = s.player_id 
AND (s.tm = nbag.home_team OR s.tm = nbag.away_team))
WHERE week = '2003-10-27' AND nbag.game_date >= '2003-10-27' 
AND nbag.game_date < '2003-11-03' 
AND (p.tm = nbag.home_team OR p.tm = nbag.away_team) 
AND p.player_id = fl.player_id AND ft.team_id = fl.team_id 
ORDER BY fl.team_id, fl.status DESC , p.player_id, nbag.game_date

That's my stab at it. I don't see a need for the outer join on nba_games, unless p.tm = nbag.home_team OR p.tm = nbag.away_team ever equals false.

For performance I'd recommend analyzing the explain output and helping the query planner by reducing the set as early as possible. That's why I changed the order in the where clause and placed what I thought were the more restricting expressions first.

chuckball

Thanks ultraslacker this worked great!