Saturday, February 26, 2011

Count nested id in sql server 2005

Suppose


i have a table treeview(tablename)  and four fields

nodeid,parentid,client_id and nodetype(it may be file or folder) 

Nodeid
Parentid
Client_id
nodetype
Folder_Name
1
1
2
Folder
A
2
1
2
Folder
B
3
1
2
Folder
C
4
2
2
Folder
D
5
2
2
Folder
E
6
3
2
Folder
F
7
3
2
File
G
8
4
2
Folder
H
9
4
2
Folder
I
10
3
2
Folder
J
11
3
2
Folder
K
12
5
2
Folder
L
13
8
2
Folder
M
14
9
2
Folder
N
15
10
2
Folder
O


These folders are nested like:

A
     B
          D
                H
                       M
                I
                       N     
          E
                L
     C
           F
           G
           J
                O
           K                 


Means ‘B’ is inside ‘A’ and ‘D’ is inside ‘B’

Now what I want to do
I want to count the folder if user click on ‘B’  it count all the folder that inside the ‘B’ and nested  in ‘B’

Show count=7 Folders

And  for the ‘C’ it count

Count=5


the query for this is:
WITH EmpCTE(nodeid, parentid, nodetype, lvl)
AS
(

  -- Anchor Member (AM)
  SELECT nodeid, nodetype, parentid, 0
  FROM treeview
  WHERE nodeid = 1
  UNION ALL
    
  -- Recursive Member (RM)
  SELECT E.nodeid, E.parentid, E.nodetype, M.lvl+1
  FROM treeview AS E
    JOIN EmpCTE AS M
      ON E.parentid = M.nodeid where e.nodetype='Folder'
)
SELECT * FROM EmpCTE

No comments:

Post a Comment