These days we are porting our JAVA-based CMS from MySQL 4.1 to 5. There was one big error we had in our statements Unknown column 'teaser.ID' in 'on clause'.

The affected statment was (simplified):

SELECT teaser.URL, meta.URL 
FROM REP_META teaser, REP_META meta
**LEFT JOIN REP_PARAM param ON teaser.ID=param.META_ID** 
WHERE teaser.ID=meta.ID;

The problem is: We placed the join clause behind the Table list. Thats bad and forbidden in MySQL 5: The JOIN clause has to appear immediate behind the table to be joined (Note: the table definitions meta and teaser had been switched):

SELECT teaser.URL, meta.URL 
FROM **REP_META meta, REP_META teaser**
LEFT JOIN REP_PARAM param ON teaser.ID=param.META_ID
WHERE teaser.ID=meta.ID;

To expose the relation it is better to format the statement like this:

SELECT teaser.URL, meta.URL 
FROM 
    REP_META meta, 
    **REP_META teaser LEFT JOIN REP_PARAM param ON teaser.ID=param.META_ID** 
WHERE teaser.ID=meta.ID;

The notation above makes it clear that the JOIN relates to the Table definition immediate before the JOIN statement.

Obviously, the JOIN mus not be placed at the end. Since the JOIN related only to the Table definition before, the following adjustment is correct and should behave identical:

SELECT teaser.URL, meta.URL 
FROM 
    **REP_META teaser LEFT JOIN REP_PARAM param ON teaser.ID=param.META_ID, **
    REP_META meta
WHERE teaser.ID=meta.ID;

Closing word: The strictness of MySQL 5 helped us to unterstand the logic (syntax and semantics) of the common used JOIN.