Problem with WHERE IN

[MarkRuddick]

I have an access DB (client choice) that has a table of players (10,000 rows) and a table of stats (100,000 rows) Each player may have played in multiple leagues and for multiple teams.

I am trying to run some advance searches. (e.g. Show me all of the players who played for TEAM A at any point in their career and show me the TOP 20 sorted by their career assists in any league or team.) This is the query I've be trying to use but it is locking up my database and not returning the data required.

SELECT TOP 20 p.id, p.first_name, p.last_name, p.player_type, SUM(s.assists) AS assists FROM player p INNER JOIN skater_stats s ON p.id = s.player_id WHERE s.player_id IN (SELECT DISTINCT ss.player_id FROM skater_stats ss WHERE (ss.stat_type = 1 OR ss.stat_type = 2) AND ss.team = 'TEAM A') AND s.stat_type = 1 Group By p.id, p.first_name, p.last_name, p.player_type ORDER BY SUM(s.assists) DESC

I can accomplish the same thing if I select all skaters for all teams (Career assists sorted) and then compare each of them to see if they played for TEAM A but this is very slow and not efficient at all. Any pointers would be appreciated.

 

[Remou]

Perhaps:

[tt]SELECT TOP 20 p.id, p.first_name, p.last_name, p.player_type, SUM(s.assists) AS assists FROM player p INNER JOIN (SELECT DISTINCT ss.player_id FROM skater_stats ss WHERE (ss.stat_type = 1 OR ss.stat_type = 2) AND ss.team = 'TEAM A') s ON p.id = s.player_id Group By p.id, p.first_name, p.last_name, p.player_type ORDER BY SUM(s.assists) DESC[/tt]