Posted by Captain Paralytic on 11/16/06 12:36

KrunoG wrote:
> "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.

But he wasn't. He was joining on the id and client fields.

[Back to original message]