Do the right thing(TM) and don't use an alias that is the same as a
field that exists (this was mentioned before by someone else).
Since there is a field in your join named "fee", using "fee" as an
alias, is bound to cause your trouble.
Also, trying to call sum(p.fee) twice is a waste of cputime. I say
trying here because this is what fails, you are not allowed to call sum
in your where clause, thus the "Invalid use of group function".
try
SELECT s.id, s.name, SUM(p.fee) AS fee_total
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 fee_total > 0
GROUP BY s.id
ORDER BY s.f_date;
and see if that works for you.
Jørn Dahl-Stamnes wrote:
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
