Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 query help

Author  Topic 

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2012-11-13 : 08:16:44
I have a table called
category
with
catid
catname
parentcatid



I want to pass in a cat id and do a query that will return to me all th parents names in order (looking by parentcatid until parentcatid is 0)

what's the best way to do it? a function?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-13 : 08:32:53
Probably use a recursive CTE
;WITH cte AS
(
SELECT catId,catName FROM Category WHERE catid = @catid
UNION ALL
SELECT c.catId,c.CatName FROM
Category c INNER JOIN cte t ON t.parentCatid = c.catId
)SELECT * FROM cte;
Go to Top of Page
   

- Advertisement -