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
