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

[andreasblixt]

That is strange... If offer and currency are the only tables with a column named currency_id, you could try changing

ON t4.currency_id = t1.currency_id

to

USING (currency_id)

and see if that clears up the problem.

[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