Problem with ON clause in MySQL 5

[Debbie-Leigh]

Hi,

My hoster has recently updated their servers to the all new version 5s of PHP and MySQL, which has caused a bit of a problem with some of my queries. It seems to be an obscure one and I can't seem to find anything in the manuals about it.

The query that I'm using is:

SELECT t1.my_col1
, t2.my_col2
, t3.my_col3
, t4.my_col4
FROM offer t1
, price_type t2
LEFT JOIN processor t3
ON t3.processor_id = ''
LEFT JOIN currency t4
ON t4.currency_id = t1.currency_id
WHERE t1.offer_id = 1
AND t1.price_type_id = t2.price_type_id

The problem is with the ON of the second LEFT JOIN. If I run it as it is i.e. comparing the two columns, it says 'Unknown column t1.currency_id', but the currency_id column definitely exists on the offer table. However, when I change that line to t4.currency_id = 1 or t1.currency_id = 1, the query works fine. The query also works fine in v4.

Obviously, I can't run it using t1.currency_id = 1, so could anyone tell me whether there was a change in the way ON clauses work between v4 and v5 of MySQL, as I can't find any indiction in the manuals about this?

Debbie

[guelphdad]

Get rid of the comma join syntax entirely. change it to an INNER JOIN

[Debbie-Leigh]

Hi,

I've found the answer, if anyone is interested.

What I needed to do was move the , price_type t2 line after the left joins, which means that the left joins are now immediately after the table they are joining onto i.e. you must keep the table grouping together now in v5.

It does make sense now that it's been pointed out and I really should have been doing this all along, but you live and learn.

Hope that helps anyone with a similar problem.

Debbie