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
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