left join issue

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

I assume that there must have been changes between the two versions,
would any one help me hilighting the mistake I make ?



--
Pierre

Re: left join issue

"Pierre" 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

Re: left join issue

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

Re: left join issue

Captain Paralytic 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

Re: left join issue

"Pierre" wrote in message
news:1howtde.qe29gkkpb2zgN%pierre.ducrot@wanadoo.fr...
> Captain Paralytic 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*/

Re: left join issue

KrunoG wrote:

> 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*/

Hi, that one works well thanks for your help.

--
Pierre

Re: left join issue

KrunoG wrote:

> 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 >

Actually, there is still something that I don't understand, you replaced
my where clause joining table i and c with a left join. However, I would
like that condition to always be joining. To my understanding, left join
returns records of c even if there would be no corresponding record in i
(same with t with the second join). Here I have a table of clients, one
of clients that are registered this year 'i' and one table that keep
track of clients tickets. In my query, I would like to return ticket
counts only for the clients that are registered this year. With your
query it returns tickets for former clients as well.

--
Pierre