Advanced Search

TSQL for Recursive Select Using Common Table Expressions

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. WITH RecursiveCategories (CategoryHint, CategoryID, CatName, CatLevel, SearchKeywords, ParentCategoryId, ePagesOID) AS
  2. (
  3.   SELECT I.CategoryHint,
  4.          C.CategoryId,
  5.          C.Name AS CatName,
  6.          C.CatLevel, 
  7.          C.SearchKeywords,
  8.          C.ParentCategoryId, 
  9.          I.ePagesOID
  10.   FROM Core.Inventory I 
  11.   LEFT JOIN Core.ProductBase PB ON PB.ProductId = I.ProductId
  12.   LEFT JOIN Core.ProductCategory PC ON PC.ProductId = PB.ProductId
  13.   LEFT JOIN Core.Category C ON C.CategoryId = PC.CategoryID
  14.   WHERE I.ePagesOID = @ePagesId
  15. UNION ALL 
  16.   SELECT RC.CategoryHint,
  17.          C.CategoryId,
  18.          C.Name AS CatName,
  19.          C.CatLevel, 
  20.          C.SearchKeywords, 
  21.          C.ParentCategoryId,
  22.          RC.ePagesOID
  23.   FROM Core.Category C 
  24.   JOIN RecursiveCategories RC ON C.CategoryID = RC.ParentCategoryID
  25.   WHERE RC.ePagesOID = @ePagesId  
  26.   AND RC.ParentCategoryId IS NOT NULL
  27. )
  28. SELECT CategoryHint, CatName, CatLevel, SearchKeywords, CategoryId, ParentCategoryId FROM RecursiveCategories ORDER BY CatLevel

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.