Posted by KrunoG on 11/17/06 07:01

"Pierre" <pierre.ducrot@wanadoo.fr> wrote in message
news:1howtde.qe29gkkpb2zgN%pierre.ducrot@wanadoo.fr...
> Captain Paralytic <paul_lautman@yahoo.com> wrote:
>
> > KrunoG wrote:
> > > 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.
>
> Indeed, I count ticktes per client, then join. Any idea why MySQL 5.x
> returns that error: #1054 - Unknown column 'c.id' in 'on clause',
> whereas 4.1.x doesn't ? I still can't figure out!
>
> --
> Pierre

In your query you're mixing the logic of sql server and oracle. If you use
sql server then joins are made within the 'from' part of the statement. In
Oracle that's made after entering all the tables wanted in the query, in
'where' part.
try this one >

select c.id, c.nom, c.prenom, t.tcs
from clients c left join inscriptions i on i.client =c.id
left join (select count(*) Tickets as 'tcs', client
as 'cly'
from tickets
group by client /*or cly*/) AS t ON
c.id=t.cly
order by c.nom, c.prenom*/

[Back to original message]