Posted by KrunoG on 11/15/06 07:27

"Pierre" <pierre.ducrot@wanadoo.fr> wrote in message
news:1hou1lo.hq2egy1ia4v2oN%pierre.ducrot@wanadoo.fr...
> Hi there,
>
> I have an issue with several LEFT JOIN ... ON like the one postes below.
> The query below executes correctly on MySQL 4.1.16-standard but fails on
> the same tables on a 5.0.19-standard server.
>
> erro: #1054 - Unknown column 'c.id' in 'on clause'
>
> --SELECT c.id, c.nom, c.prenom, t.Tickets
> FROM clients c, inscriptions i
> LEFT JOIN (
> SELECT COUNT( * ) Tickets,
> CLIENT FROM tickets
> GROUP BY CLIENT
> ) AS t ON c.id = t.client
> WHERE i.client = c.id
> ORDER BY c.nom, c.prenom
>

/*try like this:

select c.id, c.nom, c.prenom, t.tickets
from clients c left join inscriptions i on i.client =c.id
left join tickets t on c.id=t.client
order by c.nom, c.prenom*/

I just don't see how could any of sql's recognize this query, 'cause there
is no way that you could successfully join "count (*) of something" with a
field in another table. Review the logic of agregate functions (count, sum,
avg...)

If you have doubles in those 'tickets' table (I suppose that one customer
could have more than one of tickets) join it like this:

select c.id, c.nom, c.prenom, t.tickets
from clients c left join inscriptions i on i.client =c.id
left join tickets t on c.id=(select tk.client
from tickets tk
group by tk.client)
order by c.nom, c.prenom*/


BR
Kruno

[Back to original message]