List:General Discussion« Previous MessageNext Message »
From:nocturnal Date:October 18 2006 2:31pm
Subject:Some trouble with a Join after upgrade from 3.23 to 5.0
View as plain text  
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
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