-----------------------------------------------------------------
Sample SportsDB SQL
-----------------------------------------------------------------

This "scrap sheet" of various SportsDB SQL queries provide some useful examples.

None of these queries are guaranteed, and are not necessarily up-to-date with the latest SportsDB version.

(In the future, we'll provide a more foolproof set of samples.)


-----------------------------------------------------------
Goal: Get event-keys for all games today in a league
-----------------------------------------------------------

SELECT 
	events.event_key, events.start_date_time
FROM 
	events, affiliations, affiliations_events
WHERE
	affiliations_events.affiliation_id = affiliations.id
	AND affiliations_events.event_id = events.id
	AND affiliations.affiliation_key = 'l.mlb.com'
	AND events.start_date_time > '2007-08-04'
	AND events.start_date_time < '2007-08-05'


-----------------------------------------------------------------
Goal: Get all season info on one event
-----------------------------------------------------------------

SELECT
	*
FROM
	events, events_sub_seasons, sub_seasons, seasons
WHERE
	    events.event_key = 'l.mlb.com-2007-e.18640'
	AND events_sub_seasons.event_id = events.id
	AND events_sub_seasons.sub_season_id = sub_seasons.id
	AND sub_seasons.season_id = seasons.id



-----------------------------------------------------------------
Goal: Get all teams and final scores for one event
-----------------------------------------------------------------

SELECT
	*
FROM
	events, participants_events
WHERE
	    events.event_key = 'l.mlb.com-2007-e.18640'
	AND participants_events.event_id = events.id


-----------------------------------------------------------------
Goal: Get all subscores for an event
-----------------------------------------------------------------

SELECT
	*
FROM
	events, participants_events, periods
WHERE
	    events.event_key = 'l.mlb.com-2007-e.18640'
	AND participants_events.event_id = events.id
	AND periods.participant_event_id = participants_events.id






-----------------------------------------------------------------
GOAL: Monitor the rushing stats of all players in a particular game.
-----------------------------------------------------------------

SELECT 
	events.event_key,
	events.start_date_time,
	persons.person_key, 
	person_dn.full_name, 
	teams.team_key, 
	team_dn.full_name, 
	american_football_rushing_stats.*,
	stats.context
from 
	stats,
	events,american_football_rushing_stats,
	persons, 
	display_names as person_dn, 
	display_names as team_dn,
	person_event_metadata,
	teams
where
	events.event_key = 'l.nfl.com-2007-e.2152'
	and stats.stat_coverage_type = 'events'
	and stats.stat_coverage_id = events.id
	and stats.stat_repository_type = 'american_football_rushing_stats'
	and stats.stat_repository_id = american_football_rushing_stats.id
	and stats.stat_holder_type = 'persons'
	and stats.stat_holder_id = persons.id
	and person_dn.entity_type = 'persons'
	and person_dn.entity_id = persons.id
	and person_event_metadata.person_id = persons.id
	and person_event_metadata.event_id = events.id
	and person_event_metadata.team_id = teams.id
	and team_dn.entity_type = 'teams'
	and team_dn.entity_id = teams.id
	

-----------------------------------------------------------------
GOAL: Get passing stats for all players from a particular team, on a game-by-game basis this season
-----------------------------------------------------------------

SELECT
	display_names.first_name,
	display_names.last_name,
	persons.person_key,
	events.event_key,
	events.start_date_time,
	american_football_passing_stats.passes_attempts,
	american_football_passing_stats.passes_completions,
	american_football_passing_stats.passes_percentage,
	positions.abbreviation

FROM
	stats, teams, persons, american_football_passing_stats, display_names, events, events_sub_seasons, sub_seasons, person_event_metadata, positions
WHERE
	    teams.team_key = 'l.nfl.com-t.1'
	AND sub_seasons.sub_season_key = '2007_season_regular'
	AND events_sub_seasons.event_id = events.id
	AND events_sub_seasons.sub_season_id = sub_seasons.id
	AND stats.stat_repository_type = 'american_football_passing_stats'
	AND stats.stat_repository_id = american_football_passing_stats.id
	AND american_football_passing_stats.passes_attempts IS NOT NULL
	AND stats.stat_holder_type = 'persons'
	AND stats.stat_holder_id = persons.id
	AND stats.stat_coverage_type = 'events'
	AND stats.stat_coverage_id = events.id
	AND stats.context = 'event'
	AND display_names.entity_type = 'persons'
	AND display_names.entity_id = persons.id
	AND person_event_metadata.person_id = persons.id
	AND person_event_metadata.event_id = events.id
	AND person_event_metadata.team_id = teams.id
	AND person_event_metadata.position_id = positions.id


-----------------------------------------------------------------
Goal: Get list of top hitters having over 100 at-bats for a given season
-----------------------------------------------------------------

SELECT
	persons.person_key,
	display_names.first_name,
	display_names.last_name,
	baseball_offensive_stats.hits,
	baseball_offensive_stats.at_bats,
	baseball_offensive_stats.rbi,
	seasons.season_key,
	sub_seasons.sub_season_type
FROM
	stats, baseball_offensive_stats, sub_seasons, seasons, persons, display_names
WHERE
	   stats.stat_repository_type = 'baseball_offensive_stats'
	AND stats.stat_repository_id = baseball_offensive_stats.id
	AND stats.stat_coverage_type = 'sub_seasons'
	AND stats.stat_coverage_id = sub_seasons.id
	AND stats.stat_holder_type = 'persons'
	AND sub_seasons.season_id = seasons.id
	AND sub_seasons.sub_season_type = 'season-regular'
	AND seasons.season_key = '2007'
	AND display_names.entity_type = 'persons'
	AND display_names.entity_id = persons.id
	AND stats.stat_holder_id = persons.id
	AND baseball_offensive_stats.at_bats > '100'
ORDER BY
	baseball_offensive_stats.hits desc
	;
	
	
-----------------------------------------------------------------
Goal: Get official post-game stats for one player for one particular game
-----------------------------------------------------------------
SELECT
	persons.person_key,
	display_names.full_name,
	american_football_passing_stats.passes_attempts,
	american_football_passing_stats.passes_completions,
	american_football_passing_stats.passes_percentage,
	american_football_passing_stats.passes_yards_gross,
	american_football_passing_stats.passes_longest,
	american_football_passing_stats.passes_touchdowns,
	american_football_passing_stats.passes_interceptions
FROM
	stats, persons, american_football_passing_stats, display_names, events
WHERE
	    persons.person_key = 'l.nfl.com-p.400'
	AND events.event_key = 'l.nfl.com-2007-e.2154'
	AND stats.stat_repository_type = 'american_football_passing_stats'
	AND stats.stat_repository_id = american_football_passing_stats.id
	AND stats.stat_holder_type = 'persons'
	AND stats.stat_holder_id = persons.id
	AND stats.stat_coverage_type = 'events'
	AND stats.stat_coverage_id = events.id
	AND stats.context = 'event'
	AND display_names.entity_type = 'persons'
	AND display_names.entity_id = persons.id
	;


NOTES:
Using this value instead of the above:
	AND stats.context = 'event-play'
should get you Manning's unofficial mid-game stats



-----------------------------------------------------------------
Goal: Get all participants in a touchdown play for an american-football game
-----------------------------------------------------------------

SELECT
	display_names.first_name,
	display_names.last_name,
	american_football_action_participants.participant_role,
	american_football_action_participants.yards_gained,
	american_football_action_plays.comment,
	american_football_event_states.period_value,
	american_football_event_states.period_time_remaining
from
	american_football_event_states,
	american_football_action_plays,
	american_football_action_participants,
	events,
	display_names
where
	events.event_key like 'l.nfl.com-2007-e.2150'
	AND american_football_event_states.event_id = events.id
	AND american_football_action_plays.american_football_event_state_id = american_football_event_states.id
	AND american_football_action_participants.american_football_action_play_id = american_football_action_plays.id
	AND american_football_action_participants.person_id = display_names.entity_id
	AND display_names.entity_type = 'persons'
	AND american_football_action_plays.score_attempt_type = 'touchdown'
ORDER BY
	american_football_event_states.sequence_number asc
	;
	

-----------------------------------------------------------------
Goal: Find player-keys for same set of players, from two different publishers:
-----------------------------------------------------------------

	SELECT 
	p_rs.person_key, 
	p_rs.publisher_id, 
	dn_rs.full_name,
	p_tsn.person_key, 
	p_tsn.publisher_id,
	dn_tsn.full_name
	from 
	persons as p_rs,
	persons as p_tsn,
	display_names as dn_rs,
	display_names as dn_tsn
	where 
	dn_rs.full_name = dn_tsn.full_name

	and dn_rs.entity_type = 'persons'
	and dn_rs.entity_id = p_rs.id
	and p_rs.publisher_id = '2'

	and dn_tsn.entity_type = 'persons'
	and dn_tsn.entity_id = p_tsn.id
	and p_tsn.publisher_id = '1'

	order by dn_rs.full_name

	
-----------------------------------------------------------
Goal: Get an itemized list of all mid-game reports of football field goal attempts
-----------------------------------------------------------

SELECT
	events.event_key,
	start_date_time,
	period_value,
	period_time_remaining,
	play_type,
	points,
	comment,
	display_names.full_name,
	american_football_action_participants.participant_role,
	american_football_action_participants.yardage,
	american_football_action_participants.score_credit
from
	american_football_event_states,
	american_football_action_plays,
	american_football_action_participants,
	events,
	display_names
where
	events.event_key like 'l.nfl.com%'
	AND american_football_action_plays.play_type = 'field-goal'
	AND american_football_event_states.context = 'event-play'
	AND american_football_event_states.event_id = events.id
	AND american_football_action_plays.american_football_event_state_id = american_football_event_states.id
	AND american_football_action_participants.american_football_action_play_id = american_football_action_plays.id
	AND display_names.entity_type = 'persons'
	AND display_names.entity_id = american_football_action_participants.person_id



-----------------------------------------------------------------
Goal: Get event metadata and current state for a baseball game
-----------------------------------------------------------------

SELECT
	events.event_key,
	events.start_date_time,
	events.event_status,
	baseball_event_states.inning_value,
	baseball_event_states.inning_half,
	baseball_event_states.outs,
	baseball_event_states.runner_on_first,
	baseball_event_states.runner_on_second,
	baseball_event_states.runner_on_third
FROM
	events, baseball_event_states
WHERE
	    events.event_key = 'l.mlb.com-2006-e.15592'
	AND baseball_event_states.event_id = events.id
     -- AND baseball_event_states.current_state = '1'



-----------------------------------------------------------------
Goal: Get latest document links for this event
-----------------------------------------------------------------

SELECT
	events.event_key,
	documents.doc_id,
	document_fixtures.fixture_key,
	document_fixtures.name
FROM
	document_fixtures_events, events, documents, document_fixtures
WHERE
	    events.event_key = 'l.mlb.com-2006-e.15592'
	AND document_fixtures_events.event_id = events.id
	AND document_fixtures_events.document_fixture_id = document_fixtures.id
	AND document_fixtures_events.latest_document_id = documents.id


	-----------------------------------------------------------------
	Goal: Get #hits for each team
	-----------------------------------------------------------------

	SELECT
		events.event_key,
		teams.team_key,
		baseball_offensive_stats.hits
	FROM
		stats, events, baseball_offensive_stats, teams
	WHERE
		    stats.stat_repository_type = 'baseball_offensive_stats'
		AND stats.stat_repository_id = baseball_offensive_stats.id
		AND events.event_key = 'l.mlb.com-2006-e.15592'
		AND stats.stat_coverage_id = events.id
		AND stats.stat_holder_type = 'teams'
		AND teams.id = stats.stat_holder_id


		-----------------------------------------------------------------
		Goal: Get team basics:
		-----------------------------------------------------------------

		SELECT
			events.event_key,
			teams.team_key,
			display_names.full_name,
			participants_events.alignment,
			participants_events.score
		FROM
			events, participants_events, display_names, teams
		WHERE
			    participants_events.event_id = events.id
			AND events.event_key = 'l.mlb.com-2006-e.15592'
			AND display_names.entity_id = participants_events.participant_id
			AND display_names.entity_type = 'team'
			AND teams.id = participants_events.participant_id	
		ORDER BY
			participants_events.alignment




		-----------------------------------------------------------------
		Goal: Get #errors for each team
		-----------------------------------------------------------------

		SELECT
			events.event_key,
			teams.team_key,
			baseball_defensive_stats.errors
		FROM
			stats, events, baseball_defensive_stats, teams
		WHERE
			    stats.stat_repository_type = 'baseball_defensive_stats'
			AND stats.stat_repository_id = baseball_defensive_stats.id
			AND events.event_key = 'l.mlb.com-2006-e.15592'
			AND stats.stat_coverage_id = events.id
			AND stats.stat_holder_type = 'teams'
			AND teams.id = stats.stat_holder_id

			-----------------------------------------------------------------
			Goal: Get Pitching Stats
			-----------------------------------------------------------------

			SELECT
				events.event_key,
				baseball_pitching_stats.event_credit,
				persons.id as person_id,
				display_names.full_name
			FROM
				stats, events, baseball_pitching_stats, persons, display_names
			WHERE
				    stats.stat_repository_type = 'baseball_pitching_stats'
				AND stats.stat_repository_id = baseball_pitching_stats.id
				AND events.event_key = 'l.mlb.com-2006-e.16692'
				AND stats.stat_coverage_id = events.id
				AND stats.stat_holder_type = 'persons'
				AND persons.id = stats.stat_holder_id
				AND baseball_pitching_stats.event_credit IS NOT NULL
				AND display_names.entity_id = stats.stat_holder_id
				AND display_names.entity_type = 'persons'

				-----------------------------------------------------------------
				Goal: Get baseball plays
				-----------------------------------------------------------------

				SELECT 
					*
				FROM
					baseball_event_states,
					events,
					baseball_action_plays
				WHERE
					    events.event_key = 'l.mlb.com-2006-e.15379'
					AND baseball_event_states.event_id = events.id
					AND baseball_action_plays.baseball_event_state_id = baseball_event_states.id


					-----------------------------------------------------------------
					Goal: Get game states
					-----------------------------------------------------------------

					SELECT 
						*
					FROM
						baseball_event_states,
						events
					WHERE
						    events.event_key = 'l.mlb.com-2006-e.15379'
						AND baseball_event_states.event_id = events.id
						AND baseball_event_states.current_state = 1


						-----------------------------------------------------------------
						Goal: Get Current Pitcher
						-----------------------------------------------------------------

						SELECT
							baseball_event_states.inning_half,
							display_names.full_name
						FROM
							baseball_event_states,
							events,
							display_names
						WHERE
							    events.event_key = 'l.mlb.com-2006-e.15379'
							AND baseball_event_states.event_id = events.id
							AND baseball_event_states.current_state = 1
							AND display_names.entity_id = baseball_event_states.pitcher_id
							AND display_names.entity_type = 'persons'

	===========================================================================================
	Queries above have been tested with the SportsDB Sample Set. Below have not, as yet.
	===========================================================================================




-----------------------------------------------------------------
Goal: Get Opposing Pitcher
-----------------------------------------------------------------

SELECT
	baseball_event_states.inning_half,
	baseball_event_states.inning_value,
	baseball_event_states.outs,
	display_names.full_name
FROM
	baseball_event_states,
	events,
	display_names
WHERE
	    events.event_key = 'l.mlb.com-2006-e.15379'
	AND baseball_event_states.event_id = events.id
	AND baseball_event_states.current_state = 0
	AND display_names.entity_id = baseball_event_states.pitcher_id
	AND display_names.entity_type = 'persons'
	AND baseball_event_states.inning_half = 'top'
ORDER BY
	baseball_event_states.sequence_number desc,
	baseball_event_states.id desc
LIMIT
	0,1


-----------------------------------------------------------------
Goal: Get all homers by a player:
-----------------------------------------------------------------

SELECT
	DATE_FORMAT(events.start_date_time, '%M %e, %Y') as date,
	dn1.full_name as batter,
	dn2.full_name as pitcher,
	baseball_event_states.inning_value as inning,
	baseball_event_states.inning_half as half,
	baseball_event_states.outs,
	baseball_action_plays.rbi,
	baseball_action_plays.notation

FROM
	display_names as dn1,
	display_names as dn2,
	baseball_event_states,
	baseball_action_plays,
	events,
	events_sub_seasons,
	sub_seasons

WHERE
	-- restrict to Darryl Strawberry
	    dn1.full_name = 'Darryl Strawberry'
	AND dn1.entity_type = 'persons'
	AND dn1.entity_id = baseball_event_states.batter_id

	-- get name of pitcher
	AND dn2.entity_type = 'persons'
	AND dn2.entity_id = baseball_event_states.pitcher_id

	-- restrict play type to 'home-run'
	AND baseball_action_plays.baseball_event_state_id = baseball_event_states.id
	AND baseball_action_plays.play_type = 'home-run'
	AND baseball_event_states.event_id = events.id

	-- restrict to 1986 Regular Season
	AND events_sub_seasons.event_id = events.id
	AND events_sub_seasons.sub_season_id = sub_seasons.id
	AND sub_seasons.sub_season_key = '1986_regular'

ORDER BY
	events.start_date_time asc,
	baseball_event_states.inning_value asc,
	baseball_event_states.inning_half desc,
	baseball_event_states.outs asc

LIMIT
	0 , 100


-----------------------------------------------------------------
Goal: Alternative to get home runs
-----------------------------------------------------------------

SELECT
	dn1.full_name as batter,
	dn2.full_name as pitcher,
	baseball_action_plays.rbi,
	baseball_action_plays.play_type,
	baseball_action_plays.rbi,
	baseball_action_plays.notation

FROM
	display_names as dn1,
	display_names as dn2,
	baseball_event_states,
	baseball_action_plays,
	events

WHERE
	    events.event_key = '$event_key'

	AND dn1.full_name = 'Darryl Strawberry'
	AND dn1.entity_type = 'persons'
	AND dn1.entity_id = baseball_event_states.batter_id

	-- get name of pitcher
	AND dn2.entity_type = 'persons'
	AND dn2.entity_id = baseball_event_states.pitcher_id

	-- restrict play type to 'home-run'
	AND baseball_action_plays.baseball_event_state_id = baseball_event_states.id
	AND baseball_action_plays.play_type = 'home-run'
	AND baseball_event_states.event_id = events.id

ORDER BY
	events.start_date_time asc,
	baseball_event_states.inning_value asc,
	baseball_event_states.inning_half desc,
	baseball_event_states.outs asc

LIMIT
	0 , 100


-----------------------------------------------------------------
Goal: Find all the documents that list team-key X as being affiliated with event-key Y
-----------------------------------------------------------------

SELECT
	documents.doc_id,
	teams_documents.team_id
from
	events,
	teams_documents,
	events_documents,
	teams,
	documents
where
	    events.event_key = 'l.mlb.com-2007-e.18424'
	AND events_documents.event_id = events.id
	AND teams_documents.document_id = events_documents.document_id
	AND events_documents.document_id = documents.id
	AND teams.team_key = 'l.mlb.com-t.6'
	AND teams_documents.team_id = teams.id


-----------------------------------------------------------------
Goal: Get Post-Game Home Runs
-----------------------------------------------------------------

SELECT
	display_names.full_name,
	baseball_offensive_stats.home_runs
from
	events,
	stats,
	baseball_offensive_stats,
	display_names
where
	    events.event_key = 'l.mlb.com-2007-e.19502'
	AND stats.stat_coverage_id = events.id
	AND stats.stat_repository_type = 'baseball_offensive_stats'
	AND stats.stat_repository_id = baseball_offensive_stats.id
	AND baseball_offensive_stats.home_runs > 0
	AND stats.stat_holder_type = 'persons'
	AND stats.context = 'event'
	AND stats.stat_holder_id = display_names.entity_id
	AND display_names.entity_type = 'persons'


-----------------------------------------------------------------
Goal: Get Mid-Game Home Runs
-----------------------------------------------------------------

SELECT
	dn1.full_name as batter,
	dn2.full_name as pitcher,
	baseball_event_states.inning_value as inning,
	baseball_event_states.inning_half as half,
	baseball_event_states.outs,
	baseball_action_plays.rbi

FROM
	display_names as dn1,
	display_names as dn2,
	baseball_event_states,
	baseball_action_plays,
	events

WHERE
	-- restrict to particular game
	    events.event_key = 'l.mlb.com-2007-e.18461'

	-- restrict play type to 'home-run'
	AND baseball_event_states.event_id = events.id
	-- AND baseball_event_states.current_state = 0
	AND baseball_action_plays.baseball_event_state_id = baseball_event_states.id
	AND baseball_action_plays.hit_type = 'home-run'

	-- restrict to player
	AND dn1.entity_type = 'persons'
	AND dn1.entity_id = baseball_event_states.batter_id

	-- get name of pitcher
	AND dn2.entity_type = 'persons'
	AND dn2.entity_id = baseball_event_states.pitcher_id


ORDER BY
	events.start_date_time asc,
	baseball_event_states.inning_value asc,
	baseball_event_states.inning_half desc,
	baseball_event_states.outs asc

LIMIT
	0 , 100


-----------------------------------------------------------------
Goal: Get offensive stat: left_on_base
-----------------------------------------------------------------

SELECT
	display_names.full_name,
	baseball_offensive_stats.left_on_base

from
	events,
	stats,
	baseball_offensive_stats,
	display_names
where
	    events.event_key = 'l.mlb.com-2007-e.19502'
	AND stats.stat_coverage_id = events.id
	AND stats.stat_repository_type = 'baseball_offensive_stats'
	AND stats.stat_repository_id = baseball_offensive_stats.id
	AND stats.stat_holder_type = 'teams'
	-- AND stats.context = 'event'
	AND stats.stat_holder_id = display_names.entity_id
	AND display_names.entity_type = 'teams'


-----------------------------------------------------------------
Goal: Get home runs
-----------------------------------------------------------------

SELECT
	*
FROM
	baseball_event_states,
	baseball_action_plays,
	events

WHERE
	-- restrict to particular game
	    events.event_key = 'l.mlb.com-2007-e.18461'

	-- restrict play type to 'home-run'
	AND baseball_event_states.event_id = events.id
	-- AND baseball_event_states.current_state = 0
	AND baseball_action_plays.baseball_event_state_id = baseball_event_states.id
	AND baseball_action_plays.play_type = 'home-run'


-----------------------------------------------------------------
Goal: Not classified yet
-----------------------------------------------------------------

SELECT  _event_states.event_id AS `event id`,
        _event_states.sequence_number AS `sequence number`,
        _event_states.id AS `event state id`,
        _plays.id AS `play id`,
        _player_teams.id AS `team id`,
        _persons.id AS `person id`,

        "   ",

        _player_team_names.full_name AS `team`,
        _player_names.full_name AS `player`,
        _positions.abbreviation AS `position`,

        _participants.participant_role,
        _participants.yardage,

        _plays.play_type,
        _plays.score_attempt_type,
        _plays.points,

        _event_states.period_value,
        _event_states.period_time_remaining,
        _event_team_names.full_name AS `team_in_possession`,
        _event_states.down,
        _event_states.distance_for_1st_down,
        _event_states.field_side,
        _event_states.field_line

-- every game has participants
FROM `american_football_action_participants` _participants

-- every participant is part of a play
    LEFT JOIN `american_football_action_plays` _plays
    	ON _plays.id = _participants.american_football_action_play_id

-- each play is part of an event state
    LEFT JOIN `american_football_event_states` _event_states
    	ON _event_states.id = _plays.american_football_event_state_id

-- each participant is a player
    LEFT JOIN `persons` _persons
    	ON _persons.id = _participants.person_id

-- each player has display name
    LEFT JOIN `display_names` _player_names
    	ON	_player_names.entity_type = "persons" AND
    		_player_names.entity_id = _persons.id

-- each player has metadata
    LEFT JOIN `person_event_metadata` _metadata
        ON  _metadata.person_id = _persons.id AND
            _metadata.event_id = _event_states.event_id

-- each player has a position
    LEFT JOIN `positions` _positions
        ON _positions.id = _metadata.position_id

-- each event has a team
    LEFT JOIN `teams` _player_teams
    	ON _player_teams.id = _metadata.team_id

-- each team has a display name
    LEFT JOIN `display_names` _player_team_names
    	ON	_player_team_names.entity_type = "teams" AND
    		_player_team_names.entity_id = _player_teams.id

-- each event has a team_in_possession
    LEFT JOIN `teams` _event_teams
    	ON _event_teams.id = _event_states.team_in_possession_id

-- each team_in_possession has a display name
    LEFT JOIN `display_names` _event_team_names
    	ON	_event_team_names.entity_type = "teams" AND
    		_event_team_names.entity_id = _event_teams.id

WHERE _event_states.event_id = 1745

-- order by the event and each specific play
ORDER BY    _event_states.event_id,
            _event_states.sequence_number,
            _plays.id;


-----------------------------------------------------------------
Goal: Get passing stats
-----------------------------------------------------------------

SELECT
       display_names.full_name,
       american_football_passing_stats.passes_attempts,
       american_football_passing_stats.passes_completions,
       american_football_passing_stats.passes_percentage,
       stats.stat_repository_type,
       stats.stat_repository_id,
       stats.stat_holder_id,
       american_football_passing_stats.id,
       display_names.id,
       teams.id,
       display_names.entity_id

FROM
       stats, teams, american_football_passing_stats, display_names
WHERE
       stats.stat_repository_type = 'stats_american_football_passing'
AND    stats.stat_repository_id = american_football_passing_stats.id
AND    stats.stat_holder_id = teams.id
AND    teams.id = display_names.entity_id