You are here

MySQL JOIN's in 5.0

Posted at 16:53 on Tue, 24th July 2007 in mysql.

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.