Complex: Query Table 1 with data from Table 2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Complex: Query Table 1 with data from Table 2

Author

Topic

JayDavis
Starting Member

2 Posts

Posted - 2010-05-03 : 19:03:05

I am working on a stats site for softball. I have been collecting data for a while and need to present a "season" view, per player, and a "tournament" view, per player.

Each player is assigned a teamid, and every statistical record carries some identifying information:

id = id for stat row
teamid = id of team player plays for
gameid = id of the game the player is playing in
tourneyid = id of the tournament to which the game belongs
season = the season, ie 2009, 2010, etc.

For a game, a player will only have 1 stat row. However, for a tournament, the player will 4 - 10 stat rows, and obviously for a season, there could be 100 or more.

What I want to do is this. I need a query that will query the player table for all of the playerid's for a given teamid, then for each player id, query they stats table and perform stats calculations. I already have the stats calculations set up on a per game basis, so that is cool. I just need the part that gets the playerid from the player table and uses that as an input for the query on the stats table.

Something like:

Select playerid from playertable where teamid=5

Loop through playerid's that were returned and do another query on the stats table.

return the dataset of the second set of queries and calculations.

Below is the query for the per game stats:


SELECT
     p.id as 'ID',
     p.playerlastname as 'Name', 
     o.innings as 'I',
     o.atbats as 'AB', 
     o.runs as 'R', 
     o.hits as 'H',
     o.singles as '1B',
     o.doubles as '2B', 
     o.triples as '3B',
     o.homeruns as 'HR', 
     o.runsbattedin as 'RBI', 
     o.baseonballs as 'BB', 
     o.strikeouts as 'K', 
     o.sacrificeflys  as 'SF', 
     o.sacrificehits as 'SH', 
     o.stolenbases as 'SB', 
     o.errors as 'E',
	cast(cast(o.hits +o.baseonballs as decimal)
        /
    case when o.atbats+o.baseonballs+o.sacrificeflys+o.sacrificehits = 0 then
		1
    else
		cast(o.atbats+o.baseonballs+o.sacrificeflys+o.sacrificehits as decimal)
	end
	as decimal(4,3)) as 'OBP',

     cast(cast(o.singles+(o.doubles*2)+(o.triples*3)+(o.homeruns*4)as decimal)
		/
	case when o.atbats= 0 then
		1
	else
		cast(o.atbats as decimal(4,3)) 
	end
	as decimal(4,3)) as 'SLG', 

	cast(cast(o.hits as decimal)
		/
	case when o.atbats=0 then
		1
	else
		cast(o.atbats as decimal(4,3))
	end
	as decimal(4,3)) as 'AVG'

from player as p 
left join offensivestats as o on p.id=o.playerid
Where o.gameid=@id
order by p.playerlastname

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-04 : 04:23:29

what you've given above is playerlevel query. if you want calculations per game level or team level join onto team or game tables based on relevant ids and group on game/team name and take totals

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources