SQL Trying to Outer Join Tables but not working

Vote count:

0

So, I've a Uni assignment and the lecturer has picked this week to be ill and unable to answer questions.

We've been given a baseball database made up of 4 tables to work with. Table structures are as follows:

TABLENAME:(column1, column2...etc)

PK = Primary Key, FK = Foreign Key

PLAYER:(num PK, name, dob, team FK, position)

GAME:(num PK, gamedate, hometeam FK, awayteam FK, homescore, awayscore)

GAMESTAT:(gamenum PK, playernum FK, homeruns, strikeout)

TEAM:(code PK, name, town, ground)

The aim of this particularly question is to obtain the name of the stadium's (ground in team table), the sum of the home runs scored on that ground, the sum of the strikeouts and then the sum of these two values within a specified date range.

My query and issue are below:

SELECT t.ground AS GROUNDPLAYED, SUM(gs.homeruns) as TOTALHOMERUNS, SUM(gs.strikeouts) AS TOTALSTRIKEOUTS, SUM(gs.homeruns + gs.strikeouts) AS COMBINEDTOTAL
FROM team t LEFT OUTER JOIN game g ON g.hometeam = t.code LEFT OUTER JOIN gamestat gs ON g.num = gs.gamenum
WHERE g.gamedate BETWEEN '7-AUG-2014' AND '13-AUG-2014'
GROUP BY t.ground;

My problem lies in the fact that I get the correct values for games played but regardless of using the LEFT OUTER JOIN, I'm not getting all the stadium's to list. I'm convinced it has to do with the fact that I have had to join to the hometeam from the GAME table and it can only pick the home stadiums based on that.

Any help you may be able to offer would be much appreciated.

edited 1 min ago

asked 8 mins ago

UnhappyStudent

1

SQL Trying to Outer Join Tables but not working