SQL Server – Accessing Computed Column in Nested Query

sql serversubquery

I am using nested sets to represent tree-type date in SQL-Server and basing what I'm doing on this article:

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ by Mike Hillyer.

In the article Hillyer uses the following SQL to return the depth of each node:

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

I would like to return a list of all nodes that have a depth of, say, 2. My attempts have been around using a sub-query but I have been unable so far to figure out where to add the check for "depth = 2". This is what I have so far:

select * from nested_category nc 
  where exists
      (select node.category_id, (COUNT(parent.name) - 1) AS depth
       from 
          nested_category AS node,
          nested_category AS parent
        where 
          node.lft between parent.lft and parent.rgt AND
          nc.category_id = category_id 
        group by 
          node.category_id, node.lft 
       ) as ???

How can this be done?

Best Answer

You just have your nesting a little wrong (and introduced an unnecessary EXISTS clause):

SELECT * FROM
(
   SELECT node.category_id, ...
) AS nested 
WHERE depth = 2;

You could also try (I think):

SELECT node.category_id, (COUNT(parent.name) - 1) AS depth
...
    group by 
      node.category_id, node.lft 
HAVING (COUNT(parent.name)-1) = 2;

But please be mindful of:

Related Question