CODEWITHSUNDEEP
sqlsql-serverhierarchical-data
Patrik
Patrik

Reputation: 1129

Get hierarchical categories of a parent category

I need a query who select me all child categorys with a given parent id. My table looks like the following:

|ID | Title | Description | ParentId
--------------------------------------
 1  | Games | ...         | NULL

and so on..

Is it possible to solve this with a sql-query?

Give me all child categories of the category games..

All child categories could have more child categories and so on..

I need this for my website menu to list products..
My Attempt Use CocoDb Go 

;WITH parent 
  AS ( SELECT ParentId 
       FROM [CocoDb].[dbo].[Categories] 
       WHERE Id = 11 ) 
,tree AS ( SELECT x.ParentId, x.Id 
           FROM [CocoDb].[dbo].[Categories] x 
                 INNER JOIN [CocoDb].[dbo].[Categories] ON x.Id = 11
            UNION ALL 
           SELECT y.Id, y.ParentId FROM [CocoDb].[dbo].[Categories] y 
             INNER JOIN [CocoDb].[dbo].[Categories] t ON y.Id = t.ParentId )

Solution:

;WITH parent 
  AS ( SELECT ParentId, Title, Id 
       FROM [CocoDb].[dbo].[Categories] 
       WHERE Id = 1
      ) 
,tree AS ( 
           SELECT x.ParentId, x.Id, x.Title 
           FROM [CocoDb].[dbo].[Categories] x 
                 INNER JOIN parent ON x.ParentId = parent.Id
           UNION ALL 
           SELECT y.ParentId, y.Id, y.Title 
           FROM [CocoDb].[dbo].[Categories] y 
            INNER JOIN tree t ON y.ParentId = t.Id 
          )
SELECT *
FROM Tree

Upvotes: 1

Views: 1552

Answers (2)

M.Ali
M.Ali

Reputation: 69524

;WITH parent AS 
    (
    SELECT Parent
    FROM Table1
    WHERE Child = @p0
    )    
,tree AS 
    (
    SELECT x.Parent, x.Child
    FROM Table1 x
    INNER JOIN parent ON x.Parent = parent.Parent
    UNION ALL
    SELECT y.Parent, y.Child
    FROM Table1 y
    INNER JOIN tree t ON y.Parent = t.Child
    )
      SELECT Parent, Child
      FROM tree

Update For your Query

;WITH parent 
  AS ( SELECT ParentId 
       FROM [CocoDb].[dbo].[Categories] 
       WHERE Id = 11 
      ) 
,tree AS ( 
           SELECT x.ParentId, x.Id 
           FROM [CocoDb].[dbo].[Categories] x 
                 INNER JOIN parent ON x.ParentId = parent.ParentId
           UNION ALL 
           SELECT y.ParentId, y.Id 
           FROM [CocoDb].[dbo].[Categories] y 
            INNER JOIN tree t ON y.ParentId = t.Id 
          )
SELECT parent, Child 
FROM Tree

Make sure you use ; before WITH like I have used in above queries.

Upvotes: 1

Amir Keshavarz
Amir Keshavarz

Reputation: 3108

SELECT C.*
FROM <TABLE> C INNER JOIN <TABLE> P
ON P.ID=C.PARENTID
WHERE PARENTID=<GIVEN PARENTID>

Upvotes: 0

Related Questions