List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:April 20 2006 4:40am
Subject:Re: where group and inner join
View as plain text  
In the last episode (Apr 20), Peter Lauri said:
> Best groupmember,
> 
> I am doing this query that works fine. 
> 
> SELECT 
>    tps.tour_player_id,   
>    sum(if(tsh.hole_number<=9, tps.strokes, '0')) AS 'front_9', 
>    sum(if(tsh.hole_number>=10, tps.strokes, '0')) AS 'back_9', 
>    sum(tps.strokes) AS 'score'
> FROM tour_player_score tps
> INNER JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id
> GROUP BY tps.tour_player_id 
> ORDER BY 
>    score, 
>    back_9;
> 
> However, I would like to just get the result where sum(tps.strokes)<90, so I
> added WHERE sum(tps.strokes)<90 after the inner join like this
> 
> SELECT 
>    tps.tour_player_id,   
>    sum(if(tsh.hole_number<=9, tps.strokes, '0')) AS 'front_9', 
>    sum(if(tsh.hole_number>=10, tps.strokes, '0')) AS 'back_9', 
>    sum(tps.strokes) AS 'score'
> FROM tour_player_score tps
> INNER JOIN tour_scorecard_hole tsh ON tps.scorecard_hole_id=tsh.id
> WHERE sum(tps.strokes) < 90
> GROUP BY tps.tour_player_id 
> ORDER BY 
>    score, 
>    back_9;

It's useful to note that SELECT statements generally work in the order
they are written.  The WHERE clause applies to the records as they are
read from the source tables, and at that point, no grouping has been
done, so there's no sum.  Try moving your filter to a HAVING clause,
which comes between GROUP BY and HAVING, and applies to the final
resultset of the table (and at that point, you do have a
sum(tps.strokes) column).
 
-- 
	Dan Nelson
	dnelson@stripped
Thread
where group and inner joinPeter Lauri20 Apr
Re: where group and inner joinDan Nelson20 Apr
  • Re: where group and inner joinDan Nelson20 Apr
    • RE: where group and inner joinPeter Lauri20 Apr
Re: where group and inner joinPeter Brawley20 Apr