Join Through Multiple Tables

[Hutts]

I have four tables in an online sports statistical programs as similar to as follows

[tt]
Table: Divisions
ID NAME
-- ----------
1 DIVISION A
2 DIVISION B

Table: Teams
ID NAME DIVISION_ID
-- ------- -----------
1 Team 1 1
2 Team 2 2

Table: Players
ID FIRST_NAME LAST_NAME TEAM_ID
-- ---------- --------- -------
1 JOHN DOE 1
2 JIM BOB 2

Table: Player_Stats
ID PLAYER_ID STAT1 STAT2
-- --------- ----- -----
1 1 5 3
2 2 6 2
[/tt]
What would be the simplest way to query the Player_Stats table by Division_ID when it is link three tables through the hirearchy or would it be simpler to update the Players table to include a Division_ID column to link individual players to a division through a shorter hirearchy?

 

[rac2]

Definitely JOIN the tables. It may seem complicated to you now but once you get the hang of it JOINs will seem simple and natural. Well maybe not, but JOINing is fundamental to using relational databases. To UPDATE the Players table would be perverse.

This will yield the stats for all of the players in Division B.

SELECT Player_Stats.*
FROM Player_Stats
JOIN Players ON Players.ID = Player_Stats.PlayerID
JOIN Teams ON Teams.ID = Players.Team_ID
WHERE Teams.Division_ID = 2

You could take the Normalization one step further and define Player_Stats table a little differently.
Table: Player_Stats
ID PLAYER_ID STAT_ID VALUE
-- --------- ----- -----
1 1 5 0.357
2 1 3 1505
3 2 6 0.7
4 2 2 22

Table: Stats
ID Description
-- ---------
2 Ejected from the game
3 Home Runs
5 Batting Average
6 Blood Alcohol Level
(Sorry, I dont know that much about sports stats.)

This allows you to add new kinds of statistics without changing the structure of the Player_Stats table.