fhc_hockey_view_team_roster

CREATE OR REPLACE VIEW fhc_hockey_view_team_roster AS
SELECT
  s.season_id,
  s.season_title,
  s.season_alias,
  l.league_id,
  l.league_name,
  l.league_alias,
  c.club_id,
  c.club_name,
  c.club_alias,
  c.club_logo,
  c.club_website,
  ct.team_id,
  ct.team_name,
  ct.team_alias,
  p.player_id,
  p.player_alias,
  p.player_forename,
  p.player_lastname,
  p.player_nation,
  tr.player_number,
  pos.position_abr,
  pos.position_name,
  pos.position_descr,
  tr.player_photo,
  tr.player_data
  FROM fhc_hockey_team_roster tr
  JOIN fhc_hockey_season s
    ON s.season_id = tr.season_id
  JOIN fhc_hockey_league l
    ON l.league_id = tr.league_id
  JOIN fhc_hockey_club_team ct
    on ct.team_id = tr.team_id
  JOIN fhc_hockey_club c
    ON c.club_id = ct.club_id
  JOIN fhc_hockey_player p
    ON p.player_id = tr.player_id
  JOIN fhc_hockey_player_positions pos
    ON tr.player_position = pos.position_id
;