Member Avatar for serkan sendur

what is the required t-sql for getting all the child categories in a parent category?
my table is like this : id | name | parentid

  • 3 Contributors
  • 6 Replies
  • 189 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by serkan sendur

Dani AI

Generated

Short summary and practical follow-ups to the thread: the table layout shown by is the classic adjacency-list (id / parentid). As demonstrated, a plain JOIN gets one child level only; the recursive CTE posted later solves full recursion. Below is a compact, practical example that (a) returns every descendant of a given category, (b) intentionally excludes the root row by seeding from its immediate children, and (c) includes a level and simple cycle check so results are safer for deep or malformed data.

CREATE PROCEDURE dbo.sp_GetDescendants
  @RootId INT
AS
BEGIN
  SET NOCOUNT ON;

  WITH Descendants AS (
    -- seed from immediate children so root is not returned
    SELECT
      c.CategoryID,
      c.ParentCategoryID,
      c.CategoryName,
      1 AS Level,
      CAST(c.CategoryID AS VARCHAR(4000)) AS Path
    FROM dbo.Categories c
    WHERE c.ParentCategoryID = @RootId

    UNION ALL

    SELECT
      c2.CategoryID,
      c2.ParentCategoryID,
      c2.CategoryName,
      d.Level + 1,
      d.Path + '.' + CAST(c2.CategoryID AS VARCHAR(20))
    FROM dbo.Categories c2
    JOIN Descendants d ON c2.ParentCategoryID = d.CategoryID
    -- prevent simple cycles by checking the path
    WHERE CHARINDEX('.' + CAST(c2.CategoryID AS VARCHAR(20)) + '.', '.' + d.Path + '.') = 0
  )
  SELECT CategoryID, ParentCategoryID, CategoryName, Level, Path
  FROM Descendants
  ORDER BY Path
  OPTION (MAXRECURSION 0);
END;

Notes and caveats:

  • For ad-hoc lookups the recursive CTE is simple and readable. Default recursion limit is 100; use OPTION (MAXRECURSION n) or 0 for no limit, but also include cycle checks to avoid infinite loops.
  • Indexing: ensure a primary key on the id column and a nonclustered index on ParentID for fast ancestor->child joins.
  • For large trees or heavy read patterns consider alternative models: a closure table (ancestor, descendant, depth) or SQL Server’s hierarchyid — both trade extra maintenance on updates for much faster descendant queries.
  • Monitor performance with the execution plan and IO/time metrics before choosing a permanent model.

Recommended Answers

All 6 Replies

Member Avatar for Ramy Mahrous 
SELECT     dbo.Category.Name, dbo.Category.ID
FROM         dbo.Category LEFT OUTER JOIN
                      dbo.Category AS Category_1 ON dbo.Category.ID = Category_1.ParentID
WHERE     (Category_1.ParentID IS NULL)
Member Avatar for Jx_Man

absolutely RamyMahrous.

Member Avatar for serkan sendur 
SELECT     dbo.Category.Name, dbo.Category.ID
FROM         dbo.Category LEFT OUTER JOIN
                      dbo.Category AS Category_1 ON dbo.Category.ID = Category_1.ParentID
WHERE     (Category_1.ParentID IS NULL)

Thanks but this is not what i want. i want to pass cat.id as parameter and get all the childrows in it.
example

catid | catname | parentid
1 | 1 | null
2 |1.1 |1
3 |1.2 |1
4 |1.1.1 |2
5 |1.2.1 |3

when i pass a catid = 1 as parameter to procedure i want to get all these records except the first main root one.

Member Avatar for Ramy Mahrous

Sorry for being late as I was in Army last two days..

SELECT     Category_1.Name
FROM         Category LEFT OUTER JOIN
                      Category AS Category_1 ON Category.ID = Category_1.ParentID
WHERE     (Category_1.ParentID = 1)
Member Avatar for serkan sendur

Sorry for being late as I was in Army last two days..

SELECT     Category_1.Name
FROM         Category LEFT OUTER JOIN
                      Category AS Category_1 ON Category.ID = Category_1.ParentID
WHERE     (Category_1.ParentID = 1)

Thanks but this is not what i wanted, i want to see the childs recursively, i want to see the grand childs, childs of grands childs and so on.

Member Avatar for serkan sendur

the t-sql below worked :

set statistics time on
set statistics io on
declare @Topid_in int -- The top level we want to resolve children for
select @topid_in = 1;
with Hierarchycte (id, ParentID, name) as	
(select id, parentid, name
	from cat
	where id = @topid_in
	union all	
select cat.id, cat.parentid	,cat.name
from cat	
inner join hierarchycte	
	on cat.parentid = hierarchycte.id)
select * from hierarchycte
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.