CosmoCode
  • Great software.

  • Bright people.

  • Happy customers!

CosmoCode GmbH
  • Home
  • Skills
  • About Us
  • References
  • Blog
  • Open Source
←
All blogposts
→

JOIN Problems on MySQL Upgrade 4.1 to 5

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 …

Detlef Hüttemann, 03/01/2007 1:06 p.m.

JOIN Problems on MySQL Upgrade 4.1 to 5

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.


Read more

  • MySQL 5 Funktionsumfang
  • Surviving the Perl/UTF-8 Madness

Contact

Thank you for your interest!
Get in contact:

CosmoCode GmbH

Prenzlauer Allee 36G
10405 Berlin

Phone: +49 30 814 50 40 70

Fax: +49 30 2809 7093


mail: info@cosmocode.de

CosmoCode GmbH  
   

© CosmoCode 2021 | Imprint | Data Privacy | Cookies verwalten

Close
Deutsch English
  • Jobs