TSQL for Recursive Select Using Common Table Expressions


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



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.