Hockey Query Help

Question

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

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: 65144 More actions · Answer 2

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 Valued Member Points: 69 More actions · Answer 3

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