I think MySQL is being a little more strict as to expressing LEFT JOINs Notice that 'a' and 'am' are NOT TOGETHER with respect to the LEFT JOIN ... ON syntax. Maybe MySQL 3.23 is lenient with this. I know MySQL 4 is not, so MySQL 5 should not either.
Try declaring article_country first then article like this: SELECT a.id, a.nr, a.parent_id, a.designation, a.designation2, a.short_description, a.road_desc, ac.activestatus_id, ac.active_date, am.menu_id, am.active_status, a.grouparticle_type, ac.bestseller FROM article_country ac, article a LEFT JOIN article_menu am ON a.id = am.article_id AND am.active_status =1 WHERE a.parent_id = '' AND ac.country_code = 'SE' AND a.id = ac.article_id AND a.grouparticle_type <>2 ORDER BY a.designation LIMIT 0 , 30; Notice that the LEFT JOIN has 'a' on the left and 'am' on the right and the ON clause has 'a' and 'am' components only. Try it out !!! ----- Original Message ----- From: nocturnal <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Wednesday, October 18, 2006 10:31:13 AM GMT-0500 US/Eastern Subject: Some trouble with a Join after upgrade from 3.23 to 5.0 Hi I moved a lot of databases from a 3.23 system to a new 5.0 system that was taking over because of hardware upgrades. I had no major problems until the last database. This query: SELECT a.id, a.nr, a.parent_id, a.designation, a.designation2, a.short_description, a.road_desc, ac.activestatus_id, ac.active_date, am.menu_id, am.active_status, a.grouparticle_type, ac.bestseller FROM article a, article_country ac LEFT JOIN article_menu am ON a.id = am.article_id AND am.active_status =1 WHERE a.parent_id = '' AND ac.country_code = 'SE' AND a.id = ac.article_id AND a.grouparticle_type <>2 ORDER BY a.designation LIMIT 0 , 30; Gives me this error: Unknown column 'a.id' in 'on clause' I'm no MySQL expert but i assumed that a.id was an alias for article.id so i checked if the column existed manually and sure enough it does exist and it is full of data identical to the database on the old 3.23 server. So now i'd like to know what needs to be updated in the application sending this query because there is obviously something incompatible between 3.23 and 5.0. I read the documentation on this link: http://dev.mysql.com/doc/refman/5.0/en/join.html and found the section describing changes made to MySQL 5.0.12. The problems is that i couldn't find any errors in the query when i read about the new JOIN syntax described. I would like some help with this if anyone has the time to just point out what is wrong with the above query so that i can make the changes in the rest of the application. I'm sure i'll see the light if someone just pushes me in the right direction on this problem. -- Med vänliga hälsningar Stefan Midjich aka nocturnal [Swehack] http://swehack.se -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
