CFL_QUERIES.md

Players

The query:

SELECT Player.CFLCentralID, 
Player.FirstName, 
Player.MiddleName, 
Player.LastName, 
Player.BirthDate, 
Player.BirthPlace, 
Player.Height, 
Player.Weight,  
Player.RookieYear, 
Player.ForeignPlayer, 
Player.SchoolID, 
Player.SchoolName, 
Position.PositionID, 
Position.PositionType AS OffenceOrDefence,
Position.Abbreviation, 
Position.Description 
FROM Player 
LEFT OUTER JOIN Position ON Position.PositionID = Player.PositionID;

Games

The query:

SELECT *
FROM apiGameSchedule 
WHERE 1=1 
ORDER BY GameDay ASC 

Penalties

The query:

SELECT StatPenaltyInfo.* 
FROM StatPenaltyInfo
JOIN Game ON Game.Season = StatPenaltyInfo.Season 
AND Game.GameTypeID = StatPenaltyInfo.GameTypeID 
AND Game.GameNum = StatPenaltyInfo.GameNum;

Passes

The query:

SELECT * 
FROM StatGamePass;

Rushes

The query:

SELECT * 
FROM StatGameRush;

Plays

SELECT * 
FROM StatPlay;

Play Details

A single Play contains many events that occur within it; for example, the kickoff at the start of a game is a Play that usually consists of a kick, a kick return and a tackle. (In this database there are even more events than just those listed for the sake of calculating net yards and other things, but you get the idea.) The query below provides a list of those events per each Play.

SELECT StatPlayDetail.*, 
StatType.StatTypeDescription, 
GameRoster.GameRosterID, GameRoster.PlayerID, GameRoster.PlayerFirstName, GameRoster.PlayerLastName, GameRoster.JerseyNumber 
FROM StatPlayDetail
JOIN StatType ON StatPlayDetail.StatTypeID = StatType.StatTypeID 
JOIN GameRoster ON GameRoster.GameRosterID = StatPlayDetail.StatCreditedTo;