CTE SQL query get full paths

Question

Hi,

I have got simple query

WITH  conn_cte ( ParentCategoryId, CategoryId, IdsPath )
        AS ( SELECT ParentCategoryId
                   ,CategoryId
                   ,CAST(ParentCategoryId AS varchar(1000))
             FROM   Ind_CategoriesConnections
             WHERE  ParentCategoryId = 0
             UNION ALL
             SELECT cc.ParentCategoryId
                   ,cc.CategoryId
                   ,CAST(IdsPath + ','
                    + CAST (cc.ParentCategoryId AS varchar(5)) AS varchar(1000))
             FROM   Ind_CategoriesConnections AS cc
                    INNER JOIN conn_cte AS conn ON conn.CategoryId = cc.ParentCategoryId
                                                   AND cc.categoryid NOT IN (
                                                   SELECT conn.Categoryid )
           )
  SELECT  x.*
  FROM    ( SELECT  t.ParentCategoryId
                   ,t.CategoryId
                   ,t.IdsPath + ',' + CAST(t.CategoryId AS varchar(5)) AS [path]
            FROM    conn_cte t
                    INNER JOIN Ind_Categories c ON t.CategoryId = c.CategoryId
                                                   AND c.CategoryViewId = 1
                                                   AND c.IsActiveYN = 1
          ) x
  ORDER BY x.path

I am interesting in query (optimal) that only return full paths from root to leaf.

for example the part of result is

Parent  Child   Path
12       16     0,8,12,16
16       17     0,8,12,16,17
17       18     0,8,12,16,17,18
17       19     0,8,12,16,17,19

zero is root 18,19 are leafs (and children), I want to ignore partly paths like 0,8,12,16 and 0,8,12,16,17 and get only full paths (ended with leafs) 0,8,12,16,17,18 and 0,8,12,16,17,19

Answer 1

You could say something like:

WHERE NOT EXISTS (SELECT * FROM conn_cte AS parents WHERE t.path LIKE parents.path + '%')

Answer 2

DECLARE @tbl TABLE
  ( 
   Id int
  ,ParentId int
  )

INSERT  INTO @tbl
        ( Id, ParentId )
VALUES  ( 0, NULL )
,       ( 8, 0 )
,       ( 12, 8 )
,       ( 16, 12 )
,       ( 17, 16 )
,       ( 18, 17 )
,       ( 19, 17 )

;
WITH  abcd
        AS (
              -- anchor
            SELECT   id
                    ,ParentID
                    ,CAST(id AS VARCHAR(100)) AS [Path]
            FROM    @tbl
            WHERE   ParentId IS NULL
            UNION ALL
              --recursive member
            SELECT  t.id
                   ,t.ParentID
                   ,CAST(a.[Path] + ',' + CAST( t.ID AS VARCHAR(100)) AS varchar(100)) AS [Path]
            FROM    @tbl AS t
                    JOIN abcd AS a ON t.ParentId = a.id
           )
SELECT  Id ,ParentID ,[Path]
FROM    abcd
WHERE   Id NOT IN ( SELECT  ParentId
                    FROM    @tbl
                    WHERE   ParentId IS NOT NULL )

Returns

Id          ParentID    Path
----------- ----------- ----------------------
18          17          0,8,12,16,17,18
19          17          0,8,12,16,17,19

The syntax is SQL Server 2008, for 2005 change the INSERT INTO @tbl ... syntax.