3
Contributors
6
Replies
8
Views
9 Years
Discussion Span
Last Post by serkan sendur
0
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)
0
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.

0

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

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.

0

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
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.