[RESOLVED] Is this left join coded properly?

sois

Hi guys, I wrote a code for league standings in a sports league. However, if the season hasn't started, I get a blank table instead of every team showing as 0-0-0.

Here is my code:

SELECT t.TEAM_ID AS TEAM_ID, TEAM_NAME,
SUM(WIN) AS WIN, SUM(LOSS) AS LOSS, SUM(TIE) AS TIE
FROM teams t LEFT JOIN team_stats s ON t.TEAM_ID = s.TEAM_ID
WHERE TEAM_LEAGUE = '1' AND WEEK <= 10 AND SEASON = 4
GROUP BY t.TEAM_ID

Why isn't this working? Shouldn't the left join at minimum show the team id's and namee? Is the aggregation causing the issues? Thanks!

Lars_Berg

select t.TEAM_ID,
       t.TEAM_NAME,
       coalesce(sum(WIN),0) as WIN,
       coalesce(sum(LOSS),0) as LOSS, 
       coalesce(sum(TIE),0) as TIE
  from teams t 
  left 
  join team_stats s 
    on t.TEAM_ID = s.TEAM_ID
   and TEAM_LEAGUE = 1 
   and WEEK <= 10 
   and SEASON = 4
 group by t.TEAM_ID

sois

Lars Berg;10931047 wrote:

select t.TEAM_ID,
       t.TEAM_NAME,
       coalesce(sum(WIN),0) as WIN,
       coalesce(sum(LOSS),0) as LOSS, 
       coalesce(sum(TIE),0) as TIE
  from teams t 
  left 
  join team_stats s 
    on t.TEAM_ID = s.TEAM_ID
   and TEAM_LEAGUE = 1 
   and WEEK <= 10 
   and SEASON = 4
 group by t.TEAM_ID

Thanks, I didn't know about coalesce, but this does not yield the correct results. Now I am seeing all the teams listed with 0 wins, losses and ties, but it ignores the TEAM_LEAGUE restriction. It is actually showing every team from every league.

Can I not use a where statement? What about having statement?

sois

Ok guys, I got this working. This article will explain a lot:
http://codespatter.com/2008/02/19/how-to-break-a-mysql-left-join/

Basically, writing the where statement (or the way Lars suggested to add additional conditions to the on statement) breaks the Left Join.

The key part of the fix for me was to use this option (WEEK <=10 or WEEK IS NULL) along with a having condition.

Here is my final fix:

SELECT t.TEAM_ID AS TEAM_ID, TEAM_NAME, TEAM_LEAGUE,
COALESCE(SUM(WIN),0) AS WIN,
COALESCE(SUM(LOSS),0) AS LOSS,
COALESCE(SUM(TIE),0) AS TIE
FROM teams t 
LEFT JOIN team_stats s 
ON t.TEAM_ID = s.TEAM_ID
AND (WEEK <=10 OR WEEK IS NULL)
AND (SEASON = 4 OR SEASON IS NULL)
GROUP BY t.TEAM_ID
HAVING TEAM_LEAGUE = 4

Thanks all!