From: Peter Brawley
Date: October 18 2006 7:42pm
Subject: Re: Some trouble with a Join after upgrade from 3.23 to 5.0
List-Archive: http://lists.mysql.com/mysql/202764
Message-Id: <453683A8.8050906@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

 >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.

The info you need is in that section: since 5.0.12 you must change comma 
joins to explciit joins (JOIN ON ...) because since that version MySQL 
complies ISO SQL standards on that point.

PB

-----

nocturnal wrote:
> 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.


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.408 / Virus Database: 268.13.5/483 - Release Date: 10/18/2006