locked
sql query for count folders (nested) RRS feed

  • Question

  • hello to all

     

    i have a table treeview(tablename)  and five fields

     

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

     

    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

     

    So what is the sql query for it.

     

     

     

    Thursday, February 24, 2011 12:48 PM

Answers

  • See Itzik Ben-Gan example to get an idea

    CREATE TABLE Employees
    (
      empid   int         NOT NULL,
      mgrid   int         NULL,
      empname varchar(25) NOT NULL,
      salary  money       NOT NULL,
      CONSTRAINT PK_Employees PRIMARY KEY(empid),
      CONSTRAINT FK_Employees_mgrid_empid
        FOREIGN KEY(mgrid)
        REFERENCES Employees(empid)
    )
    CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
    SET NOCOUNT ON
    INSERT INTO Employees VALUES(1 , NULL, 'Nancy'   , $10000.00)
    INSERT INTO Employees VALUES(2 , 1   , 'Andrew'  , $5000.00)
    INSERT INTO Employees VALUES(3 , 1   , 'Janet'   , $5000.00)
    INSERT INTO Employees VALUES(4 , 1   , 'Margaret', $5000.00)
    INSERT INTO Employees VALUES(5 , 2   , 'Steven'  , $2500.00)
    INSERT INTO Employees VALUES(6 , 2   , 'Michael' , $2500.00)
    INSERT INTO Employees VALUES(7 , 3   , 'Robert'  , $2500.00)
    INSERT INTO Employees VALUES(8 , 3   , 'Laura'   , $2500.00)
    INSERT INTO Employees VALUES(9 , 3   , 'Ann'     , $2500.00)
    INSERT INTO Employees VALUES(10, 4   , 'Ina'     , $2500.00)
    INSERT INTO Employees VALUES(11, 7   , 'David'   , $2000.00)
    INSERT INTO Employees VALUES(12, 7   , 'Ron'     , $2000.00)
    INSERT INTO Employees VALUES(13, 7   , 'Dan'     , $2000.00)
    INSERT INTO Employees VALUES(14, 11  , 'James'   , $1500.00)

    -----------------------------
    The first request is probably the most common one:
     returning an employee (for example, Robert whose empid=7)
    and his/her subordinates in all levels.
    The following CTE provides a solution to this request:

    WITH EmpCTE(empid, empname, mgrid, lvl)
    AS
    (

      -- Anchor Member (AM)
      SELECT empid, empname, mgrid, 0
      FROM Employees
      WHERE empid = 7
      UNION ALL
     
      -- Recursive Member (RM)
      SELECT E.empid, E.empname, E.mgrid, M.lvl+1
      FROM Employees AS E
        JOIN EmpCTE AS M
          ON E.mgrid = M.empid
    )
    SELECT * FROM EmpCTE
    --------------------------------------
    Using this level counter you can limit the number of iterations
     in the recursion. For example, the following CTE is used to return
    all employees who are two levels below Janet:

    WITH EmpCTEJanet(empid, empname, mgrid, lvl)
    AS
    (
      SELECT empid, empname, mgrid, 0
      FROM Employees
      WHERE empid = 3
      UNION ALL
     
      SELECT E.empid, E.empname, E.mgrid, M.lvl+1
      FROM Employees as E
        JOIN EmpCTEJanet as M
          ON E.mgrid = M.empid
      WHERE lvl < 2
    )
    SELECT empid, empname
    FROM EmpCTEJanet
    WHERE lvl = 2

    ------------------------------------------
    As mentioned earlier, CTEs can refer to
     local variables that are defined within the same batch.
     For example, to make the query more generic, you can use
    variables instead of constants for employee ID and level:

    DECLARE @empid AS INT, @lvl AS INT
    SET @empid = 3 -- Janet
    SET @lvl   = 2 -- two levels
    WITH EmpCTE(empid, empname, mgrid, lvl)
    AS
    (
      SELECT empid, empname, mgrid, 0
      FROM Employees
      WHERE empid = @empid
      UNION ALL
     
      SELECT E.empid, E.empname, E.mgrid, M.lvl+1
      FROM Employees as E
        JOIN EmpCTE as M
          ON E.mgrid = M.empid
      WHERE lvl < @lvl
    )
    SELECT empid, empname
    FROM EmpCTE
    WHERE lvl = @lvl
    -----------------------------------------------
    Results generated thus far might be returned (but are not guaranteed to be),
    and error 530 is generated. You might think of using the MAXRECURSION option
    to implement the request to return employees who are two levels below
    Janet using the MAXRECURSION hint instead of the filter in the recursive member

    WITH EmpCTE(empid, empname, mgrid, lvl)
    AS
    (
      SELECT empid, empname, mgrid, 0
      FROM Employees
      WHERE empid = 1
      UNION ALL

      SELECT E.empid, E.empname, E.mgrid, M.lvl+1
      FROM Employees as E
        JOIN EmpCTE as M
          ON E.mgrid = M.empid
    )
    SELECT * FROM EmpCTE
    OPTION (MAXRECURSION 2)
    ------------------------------------------------

    WITH EmpCTE(empid, empname, mgrid, lvl, sortcol)
    AS
    (
      SELECT empid, empname, mgrid, 0,
        CAST(empid AS VARBINARY(900))
      FROM Employees
      WHERE empid = 1
      UNION ALL
      SELECT E.empid, E.empname, E.mgrid, M.lvl+1,
        CAST(sortcol + CAST(E.empid AS BINARY(4)) AS VARBINARY(900))
      FROM Employees AS E
        JOIN EmpCTE AS M
          ON E.mgrid = M.empid
    )
    SELECT
      REPLICATE(' | ', lvl)
        + '(' + (CAST(empid AS VARCHAR(10))) + ') '
        + empname AS empname
    FROM EmpCTE
    ORDER BY sortcol
    (1) Nancy
     | (2) Andrew
     |  | (5) Steven
     |  | (6) Michael
     | (3) Janet
     |  | (7) Robert
     |  |  | (11) David
     |  |  |  | (14) James
     |  |  | (12) Ron
     |  |  | (13) Dan
     |  | (8) Laura
     |  | (9) Ann

     | (4) Margaret
     |  | (10) Ina


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by nikijain Thursday, February 24, 2011 1:20 PM
    Thursday, February 24, 2011 1:03 PM

All replies

  • See Itzik Ben-Gan example to get an idea

    CREATE TABLE Employees
    (
      empid   int         NOT NULL,
      mgrid   int         NULL,
      empname varchar(25) NOT NULL,
      salary  money       NOT NULL,
      CONSTRAINT PK_Employees PRIMARY KEY(empid),
      CONSTRAINT FK_Employees_mgrid_empid
        FOREIGN KEY(mgrid)
        REFERENCES Employees(empid)
    )
    CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
    SET NOCOUNT ON
    INSERT INTO Employees VALUES(1 , NULL, 'Nancy'   , $10000.00)
    INSERT INTO Employees VALUES(2 , 1   , 'Andrew'  , $5000.00)
    INSERT INTO Employees VALUES(3 , 1   , 'Janet'   , $5000.00)
    INSERT INTO Employees VALUES(4 , 1   , 'Margaret', $5000.00)
    INSERT INTO Employees VALUES(5 , 2   , 'Steven'  , $2500.00)
    INSERT INTO Employees VALUES(6 , 2   , 'Michael' , $2500.00)
    INSERT INTO Employees VALUES(7 , 3   , 'Robert'  , $2500.00)
    INSERT INTO Employees VALUES(8 , 3   , 'Laura'   , $2500.00)
    INSERT INTO Employees VALUES(9 , 3   , 'Ann'     , $2500.00)
    INSERT INTO Employees VALUES(10, 4   , 'Ina'     , $2500.00)
    INSERT INTO Employees VALUES(11, 7   , 'David'   , $2000.00)
    INSERT INTO Employees VALUES(12, 7   , 'Ron'     , $2000.00)
    INSERT INTO Employees VALUES(13, 7   , 'Dan'     , $2000.00)
    INSERT INTO Employees VALUES(14, 11  , 'James'   , $1500.00)

    -----------------------------
    The first request is probably the most common one:
     returning an employee (for example, Robert whose empid=7)
    and his/her subordinates in all levels.
    The following CTE provides a solution to this request:

    WITH EmpCTE(empid, empname, mgrid, lvl)
    AS
    (

      -- Anchor Member (AM)
      SELECT empid, empname, mgrid, 0
      FROM Employees
      WHERE empid = 7
      UNION ALL
     
      -- Recursive Member (RM)
      SELECT E.empid, E.empname, E.mgrid, M.lvl+1
      FROM Employees AS E
        JOIN EmpCTE AS M
          ON E.mgrid = M.empid
    )
    SELECT * FROM EmpCTE
    --------------------------------------
    Using this level counter you can limit the number of iterations
     in the recursion. For example, the following CTE is used to return
    all employees who are two levels below Janet:

    WITH EmpCTEJanet(empid, empname, mgrid, lvl)
    AS
    (
      SELECT empid, empname, mgrid, 0
      FROM Employees
      WHERE empid = 3
      UNION ALL
     
      SELECT E.empid, E.empname, E.mgrid, M.lvl+1
      FROM Employees as E
        JOIN EmpCTEJanet as M
          ON E.mgrid = M.empid
      WHERE lvl < 2
    )
    SELECT empid, empname
    FROM EmpCTEJanet
    WHERE lvl = 2

    ------------------------------------------
    As mentioned earlier, CTEs can refer to
     local variables that are defined within the same batch.
     For example, to make the query more generic, you can use
    variables instead of constants for employee ID and level:

    DECLARE @empid AS INT, @lvl AS INT
    SET @empid = 3 -- Janet
    SET @lvl   = 2 -- two levels
    WITH EmpCTE(empid, empname, mgrid, lvl)
    AS
    (
      SELECT empid, empname, mgrid, 0
      FROM Employees
      WHERE empid = @empid
      UNION ALL
     
      SELECT E.empid, E.empname, E.mgrid, M.lvl+1
      FROM Employees as E
        JOIN EmpCTE as M
          ON E.mgrid = M.empid
      WHERE lvl < @lvl
    )
    SELECT empid, empname
    FROM EmpCTE
    WHERE lvl = @lvl
    -----------------------------------------------
    Results generated thus far might be returned (but are not guaranteed to be),
    and error 530 is generated. You might think of using the MAXRECURSION option
    to implement the request to return employees who are two levels below
    Janet using the MAXRECURSION hint instead of the filter in the recursive member

    WITH EmpCTE(empid, empname, mgrid, lvl)
    AS
    (
      SELECT empid, empname, mgrid, 0
      FROM Employees
      WHERE empid = 1
      UNION ALL

      SELECT E.empid, E.empname, E.mgrid, M.lvl+1
      FROM Employees as E
        JOIN EmpCTE as M
          ON E.mgrid = M.empid
    )
    SELECT * FROM EmpCTE
    OPTION (MAXRECURSION 2)
    ------------------------------------------------

    WITH EmpCTE(empid, empname, mgrid, lvl, sortcol)
    AS
    (
      SELECT empid, empname, mgrid, 0,
        CAST(empid AS VARBINARY(900))
      FROM Employees
      WHERE empid = 1
      UNION ALL
      SELECT E.empid, E.empname, E.mgrid, M.lvl+1,
        CAST(sortcol + CAST(E.empid AS BINARY(4)) AS VARBINARY(900))
      FROM Employees AS E
        JOIN EmpCTE AS M
          ON E.mgrid = M.empid
    )
    SELECT
      REPLICATE(' | ', lvl)
        + '(' + (CAST(empid AS VARCHAR(10))) + ') '
        + empname AS empname
    FROM EmpCTE
    ORDER BY sortcol
    (1) Nancy
     | (2) Andrew
     |  | (5) Steven
     |  | (6) Michael
     | (3) Janet
     |  | (7) Robert
     |  |  | (11) David
     |  |  |  | (14) James
     |  |  | (12) Ron
     |  |  | (13) Dan
     |  | (8) Laura
     |  | (9) Ann

     | (4) Margaret
     |  | (10) Ina


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by nikijain Thursday, February 24, 2011 1:20 PM
    Thursday, February 24, 2011 1:03 PM
  • Thanks for the reply..it works great..

    Really this sql forum is best..

     

    always i got perfect answer.

     

    I also wants to make my sql strong. Is there any site or anything through which i can understand sql easily

    Thursday, February 24, 2011 1:23 PM