get all the child categories in the parent category

[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

[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)

[Jx_Man]

absolutely RamyMahrous.

[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.

[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)

[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.

[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