Recursive querying of hierarchical data using CTE

Question

I have two tables

CREATE TABLE [dbo].[Folder](
  [FolderID] [int] IDENTITY(1,1) NOT NULL,
  [FolderName] [varchar](50) NOT NULL,
  [ParentFolderID] [int] NULL
)

and

CREATE TABLE [dbo].[FolderSubscription](
  [UserID] [int] NOT NULL,
  [FolderID] [int] NOT NULL,
  [IncludeSubfolders] [bit] NOT NULL
)

The [IncludeSubfolders] field means that if a user has subscribed subfolders also.

My goal is to get to know if a user has subscribed to a particular folder.

I tried the following recursive query. but no luck

DECLARE @FolderID INT=2,
        @UserID INT= 1
WITH SubFolderS_CTE (FolderID,ParentFolderID,FolderSubscription)
AS
(
        SELECT 
            Folder.FolderID,
            Folder.ParentFolderID,
            DMSsubscription.IncludeSubfolders
        FROM 
            FolderSubscription
        INNER JOIN 
            Folder
        ON 
            Folder.FolderID = FolderSubscription.FolderID
        WHERE 
            FolderSubscription.FolderID = @FolderID AND FolderSubscription.UserID = @UserID

        UNION ALL
        SELECT
            folder.FolderID,
            folder.ParentFolderID
            ,sub.IncludeSubfolders
        FROM
            FolderSubscription sub
        INNER JOIN
                Folder folder
        ON   folder.FolderID = sub.FolderID
        INNER JOIN
            SubFolderS_CTE
        ON folder.FolderID = SubFolderS_CTE.ParentFolderID
)
SELECT * FROM SubFolderS_CTE

Data..

FolderID |  FolderName |  ParentFolderID
----------------------------------------
   1     |     Po      |     NULL       
   2     |   Tigress   |      1         
   3     |    Mantis   |      2         

Subscription..

 UserID  |   FolderID  |  IncludeChildren
----------------------------------------
   1     |     2       |      1       

Answer 1

If you can't or don't like to use a hierarchyid as proposed by @CResults, following might get you started using plain CTE's.

DECLARE @IsSubscribedToFolderID INT = 2
DECLARE @UserID INT = 1

/* Test Data */
;WITH Folder (FolderID, FolderName, ParentFolderID) AS (
    SELECT  1, 'Po', NULL
    UNION ALL SELECT 2, 'Tigress', 1
    UNION ALL SELECT 3, 'Mantis', 2
)
, FolderSubscription (UserID, FolderID, IncludeChildren) AS (
    SELECT  1, 2, 1
)
/* Actual Query */
, q AS (
    SELECT  f.FolderID
            , [MasterFolderID] = f.FolderID
    FROM    Folder f
            INNER JOIN FolderSubscription fs ON fs.FolderID = f.FolderID
    WHERE   fs.UserID = @UserID
    UNION ALL
    SELECT  f.FolderID
            , q.MasterFolderID
    FROM    q
            INNER JOIN Folder f ON f.ParentFolderID = q.FolderID
            INNER JOIN FolderSubscription fs ON fs.FolderID = q.MasterFolderID AND fs.IncludeChildren = 1)
SELECT  q.*
FROM    q
        INNER JOIN Folder f ON f.FolderID = q.FolderID
WHERE   q.FolderID = @IsSubscribedToFolderID

Answer 2

Consider using the hierarchyid datatype. It makes queries such as this so much easier. Looking to see if one folder is the descendent of another is then just a where clause rather than lots of recursion

Unfortunately though its a SQL2008+ only option which from your tags may stop you from using it