Table alias + left join

Question

So guys I'm trying to implement nested set model and I have the following query

SELECT 
    n.CategoryID, 
    CategoriesI18n.CategoryName, 
    COUNT(*)-1 AS level FROM Categories AS n, 
    Categories AS p LEFT JOIN CategoriesI18n ON (
        n.CategoryID=CategoriesI18n.CategoryID AND CategoriesI18n.Locale="en-US"
    ) WHERE n.lft BETWEEN p.lft AND p.rgt GROUP BY n.lft ORDER BY n.lft;

When I select the tree I have to join CategoriesI18n table which is the multilanguage row for current category. But I get this error

Unknown column 'n.CategoryID' in 'on clause'

And I dont know why. Maybe Categories AS p overwrites the first alias?

Answer 1

Nevermind I figured it out. Had to do nested select.

SELECT n.CategoryID, n.CategoryName, COUNT(*)-1 AS level 
    FROM (
        SELECT Categories.CategoryID AS CategoryID,  Categories.lft AS lft,  Categories.rgt AS rgt, CategoriesI18n.CategoryName AS CategoryName FROM Categories LEFT JOIN CategoriesI18n ON (Categories.CategoryID=CategoriesI18n.CategoryID AND CategoriesI18n.Locale="bg-BG")
        ) AS n, Categories AS p 
    WHERE n.lft BETWEEN p.lft AND p.rgt GROUP BY n.lft ORDER BY n.lft;