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 ([EMAIL PROTECTED])
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
/_/ /_/\_, /___/\___\_\___/ Stockholm
<___/ www.mysql.com Cellphone: +46 708 608121
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
