MySQL JOIN’s in 5.0

A quick fix for JOIN’s written for 4.0, but needing to be compatible with 5.0:

4.0 JOIN

SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

The 4.0 syntax on a 5.0 server issues the error “Unknown column ‘t1.i1′ in ‘on clause’”.

To fix this, simply wrap the comma separated tables, in parentheses:

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

The changes are a move to standardise MySQL to SQL:2003.

More information on the MySQL Dev Documentation page.

Why am I writting about this? Well I came across this fix today, after a host for a clients website decided to upgrade their MySQL Server. It was rather unexpected, but was simple enough to fix, given half the site (where there were no JOIN’s) worked fine.