Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
I have a table calledcategorywith catidcatnameparentcatidI 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;