[SOLVED] Unknown column error when column exists

[anatak]

Hello,

 

I am totally at a loss here.

I have a query

 

SELECT content_cat_m2m_sub_cat.CategoryId as CatId, content_category.Lan_1 as CatLan_1, content_cat_m2m_sub_cat.SubCategoryId as SubCatId, content_sub_category.Lan_1 as SubCatLan_1, content_sub_cat_m2m_element.ElementId, content_element.Lan_1 as Element_1 FROM content_cat_m2m_sub_cat, content_sub_cat_m2m_element LEFT JOIN content_category ON content_cat_m2m_sub_cat.CategoryId = content_category.Id LEFT JOIN content_sub_category ON content_cat_m2m_sub_cat.SubCategoryId = content_sub_category.Id LEFT JOIN content_element ON content_sub_cat_m2m_element.ElementId = content_element.Id WHERE content_cat_m2m_sub_cat.SubCategoryId = content_sub_cat_m2m_element.SubCategoryId ORDER BY CatLan_1, SubCatLan_1, Element_1 ASC ;

 

that works on my local (my pc) database but gives an error on my webserver database (the tables are exported from local to webserver) so that is already something I don't understand.

 

the error I get is

#1054 - Unknown column 'content_cat_m2m_sub_cat.CategoryId' in 'on clause'

in this line

LEFT JOIN content_category ON content_cat_m2m_sub_cat.CategoryId = content_category.Id

 

When I run this query I don't get the error ( it has the same on close)

 

SELECT content_cat_m2m_sub_cat.CategoryId as CatId, content_category.Lan_1 as CatLan_1, content_cat_m2m_sub_cat.SubCategoryId as SubCatId, content_sub_category.Lan_1 as SubCatLan_1

FROM content_cat_m2m_sub_cat

LEFT JOIN content_category ON content_cat_m2m_sub_cat.CategoryId = content_category.Id

LEFT JOIN content_sub_category ON content_cat_m2m_sub_cat.SubCategoryId = content_sub_category.Id

ORDER BY CatLan_1, SubCatLan_1 ASC ;

 

Can anyone offer some advice ?

 

anatak

[fenway]

My guess is that the one that doesn't work runs on MySQL 5 -- the precedence rules for the comma operator have changed.  Try:

 

SELECT content_cat_m2m_sub_cat.CategoryId as CatId, content_category.Lan_1 as CatLan_1, content_cat_m2m_sub_cat.SubCategoryId as SubCatId, content_sub_category.Lan_1 as SubCatLan_1, content_sub_cat_m2m_element.ElementId, content_element.Lan_1 as Element_1 FROM (content_cat_m2m_sub_cat, content_sub_cat_m2m_element) LEFT JOIN content_category ON content_cat_m2m_sub_cat.CategoryId = content_category.Id LEFT JOIN content_sub_category ON content_cat_m2m_sub_cat.SubCategoryId = content_sub_category.Id LEFT JOIN content_element ON content_sub_cat_m2m_element.ElementId = content_element.Id WHERE content_cat_m2m_sub_cat.SubCategoryId = content_sub_cat_m2m_element.SubCategoryId ORDER BY CatLan_1, SubCatLan_1, Element_1 ASC ;

[anatak]

That was indeed the solution.

Thanks a lot fenway.

 

do you know the url of the mysql document that explains these changes ?

 

anatak

[fenway]

http://dev.mysql.com/doc/refman/5.0/en/join.html

 

Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.