Posted By

rengber on 03/24/08

Tagged

Versions (?)

Last Edited at 03/24/08 06:00pm

Statistics

Viewed 729 times

Favorited by 0 user(s)

Related snippets

TSQL for Recursive Select Using Common Table Expressions

/ Published in: SQL

Highlighted TextExpand

WITH RecursiveCategories (CategoryHint, CategoryID, CatName, CatLevel, SearchKeywords, ParentCategoryId, ePagesOID) AS
(
  Select I.CategoryHint,
         C.CategoryId,
         C.Name as CatName,
         C.CatLevel, 
         C.SearchKeywords,
         C.ParentCategoryId, 
         I.ePagesOID
  From Core.Inventory I 
  left join Core.ProductBase PB on PB.ProductId = I.ProductId
  left join Core.ProductCategory PC on PC.ProductId = PB.ProductId
  left join Core.Category C on C.CategoryId = PC.CategoryID
  Where I.ePagesOID = @ePagesId
UNION ALL 
  Select RC.CategoryHint,
         C.CategoryId,
         C.Name as CatName,
         C.CatLevel, 
         C.SearchKeywords, 
         C.ParentCategoryId,
         RC.ePagesOID
  From Core.Category C 
  join RecursiveCategories RC on C.CategoryID = RC.ParentCategoryID
  Where RC.ePagesOID = @ePagesId  
  and RC.ParentCategoryId is not null
)
Select CategoryHint, CatName, CatLevel, SearchKeywords, CategoryId, ParentCategoryId from RecursiveCategories ORDER BY CatLevel

URL: http://www.sqlservercentral.com/articles/Development/recursivequeriesinsqlserver2005/1760/

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.