Complex Query

[Leonard_Banks]

I have 2 tables:

 

Game

-game_id

-venue_id

-season_id

 

Points

-point_id

-game_id

-player_id

 

 

This query gets me what I want but it takes ~60 seconds.

SELECT sum( P.points )  from Points as P, Game as G where G.season_id='8' and P.game_id=G.game_id and G.venue_id in ( SELECT distinct G.venue_id from Points as P, Game as G where G.season_id='8' and P.player_id = '8963' and P.game_id=G.game_id )

 

 

What this accomplishes is gives me the total points at the venues where the player has played.

 

Anyone have any better options for this?

[kickstart]

Hi

 

Quick play, and not sure it can be done without a subselect of some kind. However using a  JOIN might be more efficient.

 

SELECT SUM(p.points)
FROM Points p
INNER JOIN Game g
ON p.game_id = g.game_id
INNER JOIN ( SELECT distinct G.venue_id 
from Points as p1 INNER JOIN Game as g1 ON and p1.game_id=g1.game_id 
where g1.season_id='8' 
and p1.player_id = '8963' ) s
ON g.venue_id = s.venue_id

 

All the best

 

Keith