Hockey Query Help

• javawanabe

  SSC Rookie

  Points: 49

  More actions

  January 6, 2005 at 6:28 pm

  #60907

  Hi all,

  I need help with a query. I have a stats table with several hundred thousand rows of hockey stats. I have been asked to produce a list of everyone who played for all six "Original-Six" teams in the sixties. (I honestly don't know if such a person exists...but should know if someone helps me and I promise to share the answer)

  The stats table has (among other columns) a player id and a team id. So I essentially need a query that selects distinct playr ids where teams are in 6 particular team ids. My limited T-SQL skill keep producing resultsets with the 2961 players who have played for ANY of the original 6 teams as opposed to only those who actually played for ALL six.

  Any help appreciated.

  JW

• Kathi Kellenberger

  SSChampion

  Points: 11811

  More actions

  January 6, 2005 at 8:39 pm

  #536322

  I wasn't able to do it in one query -- maybe someone else will figure that out.

  Here is an example involving 2 steps:

  create table stats (playerID int, teamID int)  -- this represents your table

  --populate with test data. 

  insert into stats select 1,1

  insert into stats select 1,2

  insert into stats select 1,3

  insert into stats select 1,4

  insert into stats select 1,5

  insert into stats select 1,6

  insert into stats select 1,7

  insert into stats select 1,8

  insert into stats select 1,9

  insert into stats select 2,1

  insert into stats select 2,2

  insert into stats select 2,3

  insert into stats select 2,4

  insert into stats select 2,1

  insert into stats select 2,2

  insert into stats select 2,3

  insert into stats select 2,4

  insert into stats select 3,1

  insert into stats select 3,2

  insert into stats select 3,3

  insert into stats select 3,4

  insert into stats select 3,5

  insert into stats select 3,6

  -- Step 1. create a table with one row for each player who played in any of

  -- the special  teams (1 - 6)

  select playerID

  into #temp

  from stats

  where teamId in (1,2,3,4,5,6)

  group by playerID, teamID

  -- here's your answer

  select playerID from #temp group by playerID having count(*) = 6

   

  playerID   

  -----------

  1

  3

  Aunt Kathi Data Platform MVP
  Author of Expert T-SQL Window Functions
  Simple-Talk Editor

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  January 7, 2005 at 6:56 am

  #536388

  select playerID

  from (select distinct playerID,teamId

  from stats

  where teamId in (1,2,3,4,5,6)) a 

  group by playerID

  having count(*) = 6

   

  Far away is close at hand in the images of elsewhere.
  Anon.

• javawanabe

  SSC Rookie

  Points: 49

  More actions

  January 7, 2005 at 5:28 pm

  #536550

  Thanks very much...that did it. And the answer is that no player ever played for all Original 6 teams, but 6 played for 5 of the teams. Thanks

  JW