Joining MySQL Nested Set to another Table

Question

I am using nested sets for categories. I understand the basic principles of finding depth and leaf nodes etc.

The site I am making is multi-lingual and so requires the category names to be displayed depending on language selected by the user. The url is constant.

The simplified tables are below...

Categories:

+----+-----+-----+-------------------------+
| id | lft | rgt | url                     |
+----+-----+-----+-------------------------+
|  1 |   1 |  12 | top_level               |
|  2 |   2 |   5 | foo                     |
|  3 |   3 |   4 | foo_sub_cat             |
|  4 |   6 |  11 | bar                     |
|  5 |   7 |   8 | bar_sub_cat_1           |
|  6 |   9 |  10 | bar_sub_cat_2           |
+----+-----+-----+-------------------------+

Category_Info:

+-------------+---------+----------------------------+
| category_id | lang_id | name                       |
+-------------+---------+----------------------------+
|          1  |       1 | One cat to rule them all   |
|          2  |       1 | Foo Cat                    |
|          3  |       1 | Subcategory of Foo         |
|          4  |       1 | Bar Cat                    |
|          5  |       1 | Bar SubCat                 |
|          6  |       1 | Another Bar SubCat         |
+-------------+---------+----------------------------+

The query I run is like this...

SELECT node.*, 
       category_info.name, 
       ( Count(parent.url) - 1 ) AS depth 
FROM   categories AS node, 
       categories AS parent 
       JOIN category_info 
         ON parent.id = category_info.category_id 
WHERE  node.lft BETWEEN parent.lft AND parent.rgt 
       AND category_info.lang_id = 1 
GROUP  BY node.url 
ORDER  BY node.lft 

In the above example, what is returned is the name One cat to rule them all for each result. Which is what you'd expect, as I'm matching the parent.id.

The JOIN is what is killing me. If I try JOIN node.id = category_info.category_id then I get an error telling me node.id is not found, same as if I use JOIN categories.id = category_info.category_id.

I know I must be close, but I really can't figure it out.

Answer 1

could you try this?

SELECT node.*, category_info.name, (COUNT(parent.url) - 1) AS depth
FROM categories AS node INNER JOIN
                        ^^^^^^^^^^^ <= this is added
    categories AS parent
JOIN category_info ON node.id = category_info.category_id
WHERE node.lft BETWEEN parent.lft AND parent.rgt AND category_info.lang_id = 1
GROUP BY node.url
ORDER BY node.lft

When JOIN is missing, the column can not be referenced in ON clause, but can be reference in WHERE part

Here is what I have tried.

mysql> SELECT * FROM t1 tab1 INNER JOIN t1 tab2 INNER JOIN t2 tab3 ON tab1.a = tab3.a;
Empty set (0.00 sec)

mysql> SELECT * FROM t1 tab1,  t1 tab2 INNER JOIN t2 tab3 ON tab1.a = tab3.a;
ERROR 1054 (42S22): Unknown column 'tab1.a' in 'on clause