Re: Problems with WHERE clause - Visolve DB Team - com.mysql.lists.mysql

7 messages in com.mysql.lists.mysqlRe: Problems with WHERE clause

From	Sent On	Attachments
Jørn Dahl-Stamnes	30 Jul 2006 02:09
Chris Sansom	30 Jul 2006 02:50
Visolve DB Team	30 Jul 2006 03:26
Gabriel PREDA	30 Jul 2006 03:37
Jørn Dahl-Stamnes	30 Jul 2006 06:34
Martin Jespersen	30 Jul 2006 06:49
Michael Stassen	30 Jul 2006 09:53

Subject:	Re: Problems with WHERE clause
From:	Visolve DB Team (mysq...@visolve.com)
Date:	07/30/2006 03:26:25 AM
List:	com.mysql.lists.mysql

Hello Jørn Dahl-Stamnes,

The column 'fee' is existing in more than one table . To overcome this problem use the correct instance name of the table for the column "fee" In WHERE clause

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 ---- Here p is the instance of the table participants group by s.id order by s.f_date;

Thanks VisolveDB Team

I got the following query:

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 fee > 0 group by s.id order by s.f_date;

which gives me the error:

ERROR 1052 (23000): Column 'fee' in where clause is ambiguous

Without the 'and fee > 0' the query works fine.

mysql> select version(); +----------------+ | version() | +----------------+ | 4.1.8-standard | +----------------+

Is this due to an old version of MySQL?