List:General Discussion« Previous MessageNext Message »
From:Anders Karlsson Date:October 18 2006 7:48pm
Subject:Re: Some trouble with a Join after upgrade from 3.23 to 5.0
View as plain text  
This is due to a change in MySQL 5.0.12 that was done to align with 
SQL:2003. Here, we started to be more conservative regarding what could 
go into the ON clause. The whole thing is documented here: 
http://dev.mysql.com/doc/refman/5.0/en/join.html
In your case, the JOIN would look something like this (Not tested, just 
straight from under the hairy stuf on the top of my head):
FROM article_country ac, article a
LEFT JOIN article_menu am ON a.id = am.article_id
Or, to be more SQL'ish:
FROM article_country ac JOIN article a
LEFT JOIN article_menu am ON a.id = am.article_id
Or, to be even more more SQL'ish (this one I tested):
FROM article_country ac CROSS JOIN article a
LEFT JOIN article_menu am ON a.id = am.article_id

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


-- 
    __  ___     ___ ____  __
   /  |/  /_ __/ __/ __ \/ /  Anders Karlsson (anders@stripped)
  / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
 /_/  /_/\_, /___/\___\_\___/ Stockholm
        <___/   www.mysql.com Cellphone: +46 708 608121


Thread
Some trouble with a Join after upgrade from 3.23 to 5.0nocturnal18 Oct
  • Re: Some trouble with a Join after upgrade from 3.23 to 5.0Rolando Edwards18 Oct
  • Re: Some trouble with a Join after upgrade from 3.23 to 5.0Peter Brawley18 Oct
  • Re: Some trouble with a Join after upgrade from 3.23 to 5.0Anders Karlsson18 Oct