How to change this CROSS JOIN SQL created for a tree traversal (nested set)?

In the query below there is a field called main which is used to differentiate the categorie开发者_如何学Cs. So, there are common categories and main categories and both can be found on a tree. The main field is just a type and there's nothing with the tree structure, I mean, it's like a status field.

  SELECT `c`.*, 
           (count(p.id)-1) AS `depth` 
      FROM `categories` AS `c` 
  CROSS JOIN `categories` AS `p` 
     WHERE (c.lft BETWEEN p.lft AND p.rht) 
       AND (c.root_id =p.root_id) 
       AND (p.main =1) 
  GROUP BY `c`.`id` 
  ORDER BY `c`.`root_id` ASC, `c`.`lft` ASC

There is a where clausule which specify that the parent category need to be a main category. Also, sometimes I need to do a select where the parent category is a common category p.main =0.

The depth is the position of the element in the tree. So if a category is a one level children of another, the depth will be 1, if two levels, the depth will be 2.

My problem is that when I do the select above, if there are children marked as common category on a tree where the father is a main category (selecting p.main =1) the common categories depth is always 0.

In other words, the select works properly, if I select all categories that has the top parent marked as main, it will display the tree with all categories including children categories marked with main=0. But in this case, the depth is always 0

See the results:

The category 1423 is a child of 27 and is not a main category, but 27 is, so depth is 0, but need to be 1. The category 276 is a child of 64 and both are a main categories, so it has the right depth.

How can I change this query so that the depth field work as expected?

Reference here: How to generate a tree view from this result set based on Tree Traversal Algorithm?

You actually say you're looking for this count but only for nodes what are children of a main parent. If that's what you truly want then your query is more wrong than just fixing that - the table you call 'p' and seem to think is a 'parent' table is actually an 'ancestor' table - similarly 'c' is not child but 'descendant'. You end up counting main-flagged ancestors of all nodes in the tree.