7 messages in com.mysql.lists.mysqlRe: Problems with WHERE clause
FromSent OnAttachments
Jørn Dahl-Stamnes30 Jul 2006 02:09 
Chris Sansom30 Jul 2006 02:50 
Visolve DB Team30 Jul 2006 03:26 
Gabriel PREDA30 Jul 2006 03:37 
Jørn Dahl-Stamnes30 Jul 2006 06:34 
Martin Jespersen30 Jul 2006 06:49 
Michael Stassen30 Jul 2006 09:53 
Subject:Re: Problems with WHERE clause
From:Jørn Dahl-Stamnes (sql@dahl-stamnes.net)
Date:07/30/2006 06:34:11 AM
List:com.mysql.lists.mysql

On Sunday 30 July 2006 12:37, Gabriel PREDA wrote:

You must specify explicitly what 'fee' to use... so if you wand every p.fee to be greater than zero then you must do:

SELECT s.id, s.name, SUM(p.fee) AS fee FROM serie AS s INNER JOIN race_serie AS rs ON rs.serie_id = s.id INNER JOIN races AS r ON r.id = rs.race_id INNER JOIN participants AS p ON p.race_id = r.id WHERE s.receipt = 1 AND p.rider_id = 236 AND p.fee > 0 GROUP BY s.id ORDER BY s.f_date;

This did the trick.

What I want is to find out which series a given rider has participated where (s)he has paid fee for participating (in some cases a rider my participate without haveing paied).

If the rider has paied fee once in a serie, then the sum will be more than 0. But will the query above give me the id and name for a serie where a rider has participated but not paid? One way to find out is to test it.

If you want the sum to be larger that zero then you would have to do:

SELECT s.id, s.name, SUM(p.fee) AS fee FROM serie AS s INNER JOIN race_serie AS rs ON rs.serie_id = s.id INNER JOIN races AS r ON r.id = rs.race_id INNER JOIN participants AS p ON p.race_id = r.id WHERE s.receipt = 1 AND p.rider_id = 236 AND SUM(p.fee) > 0 GROUP BY s.id ORDER BY s.f_date;

This gave the following error: ERROR 1111 (HY000): Invalid use of group function

-- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/