CODEWITHSUNDEEP
phpmysql
Vadiklk
Vadiklk

Reputation: 3764

Why do i get a cartesian product instead of join?

The following code should return me the average numbers the first five players did in a game.

SELECT "Starting 5" as Flag, 
       count(gs.ID) as Games, 
       avg(s.min) as Min, 
       avg(s.P2M) as P2M, 
       avg(s.P2A) as P2A, 
       100*avg(s.P2M/s.P2A) as P2P,  
       avg(s.P3M) as P3M,  
       avg(s.P3A) as P3A,  
       100*avg(s.P3M/s.P3A) as P3P,  
       avg(s.FTM) as FTM,  
       avg(s.FTA) as FTA,  
       100*avg(s.FTM/s.FTA) as FTP,  
       avg(s.OFFENSE) as OFFENSE,  
       avg(s.DEF) as DEF,  
       avg(s.TOT) as TOT,  
       avg(s.AST) as AST,  
       avg(s.TO) as 'TO',  
       avg(s.ST) as ST,  
       avg(s.FV) as FV,  
       avg(s.CM) as CM,  
       avg(s.PTS) as PTS,  
       avg(s.P2M - s.P2A + s.P3M - s.P3A + s.FTM - s.FTA + s.TOT + s.AST + s.ST + s.FV + s.PTS - s.TO - s.CM) as EFF  
FROM gamesstats AS gs 
    INNER JOIN gamesstatsplayers as gp ON gs.id=gp.GameStatID 
    INNER JOIN players as p ON gp.PlayerID = p.ID 
    INNER JOIN stats as s ON gp.stat=s.id 
                         WHERE gs.HomeTeamID ='664' 
                         and gs.HomeScore > gs.VisitorScore 
                         and gp.IsHomePlayer = 1 
                         AND gp.IsFirst5 = 1 
                         AND gs.SeasonStart = '2010' 
                         AND gs.LeagueID = '145'

I should get 4 games and the averages in each. What I get is the avg of each player in a game. That means I get 20 games and 1/5 of the average I want in each. The problem is for some reason it makes a cartesian product instead of join. Was tryign to find the bug for several hours but can't seem to find it.

The table explanations:

gamesstats is for holding each game.

gamesstatsplayers is has a connection to a game via GameStatID and is responsible for each player stats in each game.

players is a table of all players there are.

stats is a table with just the stats, gamesstats has a link to stats to give you the stats of a whole team in a game(2 links, one for home and one for visitor), gamesstatsplayers has a link to stats to give you the stats of 1 player in 1 game. (That means stats is for two things).

Upvotes: 0

Views: 126

Answers (2)

CristiC
CristiC

Reputation: 22698

First, you should use WHERE clause:

SELECT "Starting 5" as Flag, 
       count(gs.ID) as Games, 
       avg(s.min) as Min, 
       avg(s.P2M) as P2M, 
       avg(s.P2A) as P2A, 
       100*avg(s.P2M/s.P2A) as P2P,  
       avg(s.P3M) as P3M,  
       avg(s.P3A) as P3A,  
       100*avg(s.P3M/s.P3A) as P3P,  
       avg(s.FTM) as FTM,  
       avg(s.FTA) as FTA,  
       100*avg(s.FTM/s.FTA) as FTP,  
       avg(s.OFFENSE) as OFFENSE,  
       avg(s.DEF) as DEF,  
       avg(s.TOT) as TOT,  
       avg(s.AST) as AST,  
       avg(s.TO) as 'TO',  
       avg(s.ST) as ST,  
       avg(s.FV) as FV,  
       avg(s.CM) as CM,  
       avg(s.PTS) as PTS,  
       avg(s.P2M - s.P2A + s.P3M - s.P3A + s.FTM - s.FTA + s.TOT + s.AST + s.ST + s.FV + s.PTS - s.TO - s.CM) as EFF  
FROM gamesstats AS gs 
    INNER JOIN gamesstatsplayers as gp ON gs.id=gp.GameStatID 
    INNER JOIN players as p ON gp.PlayerID = p.ID 
    INNER JOIN stats as s ON gp.stat=s.id 
    WHERE
     gs.HomeTeamID ='664' 
     and gs.HomeScore > gs.VisitorScore 
     and gp.IsHomePlayer = 1 
     AND gp.IsFirst5 = 1 
     AND gs.SeasonStart = '2010' 
     AND gs.LeagueID = '145'

Upvotes: 0

Paul W
Paul W

Reputation: 1049

without knowing the schema, this is a guess, but you probably need to group on the GameStatID, if that is the primary key for the game.

Upvotes: 1

Related Questions