MYSQL Join question

Forum Moderators: open

Message Too Old, No Replies

MYSQL Join question

3 tables with all sorts of requirements

mooger35

11:40 pm on Jan 28, 2008 (gmt 0)

Help!

What I have in MYSQL:
table 1: players
Fields: player_id, name

table 2: roster
Fields: roster_id, season, squad, player_id

table 3: stats
Fields: stats_id, season, squad, game_id, player_id, goals, assists

What I need to do is pull the name, sum(goals) and sum(assists) for each player listed in roster for season = '2008' and squad = 's'

The major problem I have is that I don't have stats for 2008 yet but I want to show 0's for the sums and list all the players from the roster of that season and squad.

I've run into a roadblock with this.

ZydoSEO

6:41 am on Jan 29, 2008 (gmt 0)

Hard to picture what type of values you're storing. I'm guessing Statistics_ID, for instance, is an AUTO-INCREMENT field... but can't tell. Anyway, I think what you need is a left join... I create similar tables... loaded 12 players, loaded the roster w/ data for 2 five man teans for 2006, 2007, and 2008, and loaded statistics for 2 games in 2006 and 2007 (no games in 2008).

Unfortunately, I don't have MySQL so I did it w/ SQL*Server... I think what you are looking for is a 3 way join which includes a LEFT OUTER JOIN to your statistics table... Look at my data and see if the JOIN at the end actually does what you're looking for... Hopefully, it's easy to translate to your exact table/field names and MySQL specific syntax.

Notice in the final results, I get back a row for every player on every team for every season regardless of whether the player had goals and/or assists or had 0 goals AND 0 assists.

-- create the tables

CREATE TABLE tPlayer
(
PlayerID INT NOT NULL,
LastName VARCHAR(16) NOT NULL,
FirstName VARCHAR(16) NOT NULL
)

CREATE TABLE tRoster
(
RosterID INT NOT NULL,
Season INT NOT NULL,
Squad CHAR NOT NULL,
PlayerID INT NOT NULL
)

CREATE TABLE tStatistics
(
StatisticsID INT NOT NULL IDENTITY, -- auto increment
Season VARCHAR(16) NOT NULL,
Squad CHAR NOT NULL,
GameID INT NOT NULL,
PlayerID INT NOT NULL,
GoalCount INT NOT NULL,
AssistCount INT NOT NULL
)

-- Load the player table with 12 players
INSERT INTO tPlayer (PlayerID,LastName, FirstName) VALUES (1,'Smith','Doug')
INSERT INTO tPlayer (PlayerID,LastName, FirstName) VALUES (2,'Thomas','John')
INSERT INTO tPlayer (PlayerID,LastName, FirstName) VALUES (3,'Jones','Rich')
INSERT INTO tPlayer (PlayerID,LastName, FirstName) VALUES (4,'Wilkes','Joel')
INSERT INTO tPlayer (PlayerID,LastName, FirstName) VALUES (5,'Lowery','Russ')
INSERT INTO tPlayer (PlayerID,LastName, FirstName) VALUES (6,'Clark','John')
INSERT INTO tPlayer (PlayerID,LastName, FirstName) VALUES (7,'Davenport','Bob')
INSERT INTO tPlayer (PlayerID,LastName, FirstName) VALUES (8,'Dabhade','Zeal')
INSERT INTO tPlayer (PlayerID,LastName, FirstName) VALUES (9,'Gardner','Ima')
INSERT INTO tPlayer (PlayerID,LastName, FirstName) VALUES (10,'Harris','Tom')
INSERT INTO tPlayer (PlayerID,LastName, FirstName) VALUES (11,'Wyke','Gomer')
INSERT INTO tPlayer (PlayerID,LastName, FirstName) VALUES (12,'Long','Mark')


SELECT * FROM tPlayer WITH (NOLOCK)PlayerID LastName   FirstName
----------- ---------------- ----------------
1   Smith   Doug
2   Thomas   John
3   Jones   Rich
4   Wilkes   Joel
5   Lowery   Russ
6   Clark   John
7   Davenport  Bob
8   Dabhade   Zeal
9   Gardner   Ima
10   Harris   Tom
11   Wyke    Gomer
12   Long    Mark(12 row(s) affected)

-----------------------------------------------------------------------------------
-- Load the roster w/ 5 players on each team, 2 teams per season for 2006,2007,2008
-----------------------------------------------------------------------------------

-- 2006 A/B rosters

INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (1,'2006','A', 1)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (1,'2006','A', 2)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (1,'2006','A', 3)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (1,'2006','A', 4)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (1,'2006','A', 5)

INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (2,'2006','B', 6)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (2,'2006','B', 7)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (2,'2006','B', 8)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (2,'2006','B', 9)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (2,'2006','B', 10)

-- 2007 A/B Rosters

INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (3,'2007','A', 1)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (3,'2007','A', 3)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (3,'2007','A', 5)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (3,'2007','A', 7)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (3,'2007','A', 9)

INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (4,'2007','B', 2)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (4,'2007','B', 6)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (4,'2007','B', 8)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (4,'2007','B', 10)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (4,'2007','B', 11)

-- 2008 A/B Rosters

INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (5,'2008','A', 1)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (5,'2008','A', 3)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (5,'2008','A', 9)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (5,'2008','A', 11)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (5,'2008','A', 12)

INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (6,'2008','B', 2)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (6,'2008','B', 5)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (6,'2008','B', 6)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (6,'2008','B', 7)
INSERT INTO tRoster (RosterID, Season, Squad, PlayerID) VALUES (6,'2008','B', 10)


SELECT * FROM tRoster WITH (NOLOCK)RosterID Season  Squad PlayerID
----------- ----------- ----- -----------
1   2006  A  1
1   2006  A  2
1   2006  A  3
1   2006  A  4
1   2006  A  5
2   2006  B  6
2   2006  B  7
2   2006  B  8
2   2006  B  9
2   2006  B  10
3   2007  A  1
3   2007  A  3
3   2007  A  5
3   2007  A  7
3   2007  A  9
4   2007  B  2
4   2007  B  6
4   2007  B  8
4   2007  B  10
4   2007  B  11
5   2008  A  1
5   2008  A  3
5   2008  A  9
5   2008  A  11
5   2008  A  12
6   2008  B  2
6   2008  B  5
6   2008  B  6
6   2008  B  7
6   2008  B  10(30 row(s) affected)

-----------------------------------------------------------------------------------
-- Load the statistics for 2 games per season for 2006,2007,2008
-----------------------------------------------------------------------------------

-- game 1 2006

INSERT INTO tStatistics (Season, Squad, GameID, PlayerID, GoalCount, AssistCount)
VALUES ('2006', 'A', 1, 1, 2, 0)
INSERT INTO tStatistics (Season, Squad, GameID, PlayerID, GoalCount, AssistCount)
VALUES ('2006', 'A', 1, 4, 0, 1)
INSERT INTO tStatistics (Season, Squad, GameID, PlayerID, GoalCount, AssistCount)
VALUES ('2006', 'A', 1, 5, 1, 1)

INSERT INTO tStatistics (Season, Squad, GameID, PlayerID, GoalCount, AssistCount)
VALUES ('2006', 'B', 1, 6, 1, 0)
INSERT INTO tStatistics (Season, Squad, GameID, PlayerID, GoalCount, AssistCount)
VALUES ('2006', 'B', 1, 9, 0, 1)
INSERT INTO tStatistics (Season, Squad, GameID, PlayerID, GoalCount, AssistCount)
VALUES ('2006', 'B', 1, 10, 1, 1)

-- game 2 2006

INSERT INTO tStatistics (Season, Squad, GameID, PlayerID, GoalCount, AssistCount)
VALUES ('2006', 'A', 2, 1, 2, 0)
INSERT INTO tStatistics (Season, Squad, GameID, PlayerID, GoalCount, AssistCount)
VALUES ('2006', 'A', 2, 4, 0, 1)

INSERT INTO tStatistics (Season, Squad, GameID, PlayerID, GoalCount, AssistCount)
VALUES ('2006', 'B', 2, 8, 2, 0)
INSERT INTO tStatistics (Season, Squad, GameID, PlayerID, GoalCount, AssistCount)
VALUES ('2006', 'B', 2, 10, 0, 1)

-- game 1 2007

INSERT INTO tStatistics (Season, Squad, GameID, PlayerID, GoalCount, AssistCount)
VALUES ('2007', 'A', 1, 1, 1, 1)
INSERT INTO tStatistics (Season, Squad, GameID, PlayerID, GoalCount, AssistCount)
VALUES ('2007', 'A', 1, 3, 3, 1)

INSERT INTO tStatistics (Season, Squad, GameID, PlayerID, GoalCount, AssistCount)
VALUES ('2007', 'B', 1, 6, 1, 0)

-- game 2 2007

INSERT INTO tStatistics (Season, Squad, GameID, PlayerID, GoalCount, AssistCount)
VALUES ('2007', 'A', 2, 1, 4, 0)
INSERT INTO tStatistics (Season, Squad, GameID, PlayerID, GoalCount, AssistCount)
VALUES ('2007', 'A', 2, 9, 0, 1)

INSERT INTO tStatistics (Season, Squad, GameID, PlayerID, GoalCount, AssistCount)
VALUES ('2007', 'B', 2, 6, 2, 0)
INSERT INTO tStatistics (Season, Squad, GameID, PlayerID, GoalCount, AssistCount)
VALUES ('2007', 'B', 2, 10, 0, 1)
INSERT INTO tStatistics (Season, Squad, GameID, PlayerID, GoalCount, AssistCount)
VALUES ('2007', 'B', 2, 11, 0, 1)


SELECT * FROM tStatistics WITH (NOLOCK)StatisticsID Season   Squad GameID  PlayerID GoalCount AssistCount
------------ ---------------- ----- ----------- ----------- ----------- -----------
1   2006    A  1   1   2   0
2   2006    A  1   4   0   1
3   2006    A  1   5   1   1
4   2006    B  1   6   1   0
5   2006    B  1   9   0   1
6   2006    B  1   10   1   1
7   2006    A  2   1   2   0
8   2006    A  2   4   0   1
9   2006    B  2   8   2   0
10   2006    B  2   10   0   1
11   2007    A  1   1   1   1
12   2007    A  1   3   3   1
13   2007    B  1   6   1   0
14   2007    A  2   1   4   0
15   2007    A  2   9   0   1
16   2007    B  2   6   2   0
17   2007    B  2   10   0   1
18   2007    B  2   11   0   1(18 row(s) affected)

SELECT r.Season, r.Squad, p.PlayerID, p.LastName, p.FirstName,
sum(ISNULL(s.GoalCount,0)) GoalCount, sum(ISNULL(s.AssistCount,0)) AssistCount
FROM tPlayer p WITH (NOLOCK)
JOIN tRoster r WITH (NOLOCK) ON p.PlayerID = r.PlayerID
LEFT JOIN tStatistics s WITH (NOLOCK) ON p.PlayerID = s.PlayerID AND r.Season = s.Season AND r.Squad=s.Squad
GROUP BY r.Season, r.Squad, p.PlayerID, p.LastName, p.FirstName
ORDER BY r.Season, r.Squad, p.Lastname, p.FirstName

Season Squad PlayerID LastName FirstName GoalCount AssistCount ----------- ----- ----------- ---------------- ---------------- ----------- ----------- 2006 A 3 Jones Rich 0 0 2006 A 5 Lowery Russ 1 1 2006 A 1 Smith Doug 4 0 2006 A 2 Thomas John 0 0 2006 A 4 Wilkes Joel 0 2 2006 B 6 Clark John 1 0 2006 B 8 Dabhade Zeal 2 0 2006 B 7 Davenport Bob 0 0 2006 B 9 Gardner Ima 0 1 2006 B 10 Harris Tom 1 2 2007 A 7 Davenport Bob 0 0 2007 A 9 Gardner Ima 0 1 2007 A 3 Jones Rich 3 1 2007 A 5 Lowery Russ 0 0 2007 A 1 Smith Doug 5 1 2007 B 6 Clark John 3 0 2007 B 8 Dabhade Zeal 0 0 2007 B 10 Harris Tom 0 1 2007 B 2 Thomas John 0 0 2007 B 11 Wyke Gomer 0 1 2008 A 9 Gardner Ima 0 0 2008 A 3 Jones Rich 0 0 2008 A 12 Long Mark 0 0 2008 A 1 Smith Doug 0 0 2008 A 11 Wyke Gomer 0 0 2008 B 6 Clark John 0 0 2008 B 7 Davenport Bob 0 0 2008 B 10 Harris Tom 0 0 2008 B 5 Lowery Russ 0 0 2008 B 2 Thomas John 0 0(30 row(s) affected)

[edited by: ZydoSEO at 6:56 am (utc) on Jan. 29, 2008]

mooger35

5:15 pm on Jan 29, 2008 (gmt 0)

Awesome!

With just a little fiddling I got what I needed.

Thanks a lot.