LEFT JOIN with multiple tables 1

[MacTommy]

Dear all,

I've got a query that does a LEFT JOIN.
Something like this:
[tt]
SELECT a.id, myB.someField, c.someColumn
FROM c, a
LEFT JOIN b AS myB ON (myB.id = a.id)
[/tt]
which works.
However, if I reverse the two tables in the FROM clause, it doesn't work anymore. It seems like the table in the ON part has to be the last table mentioned in the FROM clause.

Curiously enough, it DOES work on MySQL 4 (4.1.16-nt), but NOT on MySQL 5 (5.0.45-community-nt).

This might not seem much of a problem, but sometimes I want to do more LEFT JOINs in one query, with different tables in the ON part. And I don't see how I can do that in MySQL 5.x, as all the tables would all have to be last in line in the FROM part.
It did work perfectly fine in MySQL 4.x though... ;-(

Am I missing something here..?!?!

Is there a way of solving this?!?

 

[MacTommy]

And in the latest version (MySQL 5.0.51a-community-nt) it doesn't work either...

 

[r937]

tip: never mix "comma-delimited" syntax with JOIN syntax

re-write your query using only JOIN syntax

SELECT a.id, myB.someField, c.someColumn
  FROM c
CROSS 
  JOIN a
LEFT OUTER
  JOIN b AS myB 
    ON myB.id = a.id

r937.com | rudy.ca

 

[MacTommy]

Allright, thanks!
This will take some time, because the original query I got features like 15 JOINs or something, some on more than one table.
And anyway, I am still I bit put back by the fact that something that worked perfectly fine in MySQL 4 suddenly doesn't work at all anymore in the next version.

But that having been said, I guess I'll work it out. Thanks again!