advanced code snippet search

We Recommend

SQL Cookbook SQL Cookbook
Written in O'Reilly's popular Problem/Solution/Discussion style, the SQL Cookbook is sure to please. Anthony's credo is: "When it comes down to it, we all go to work, we all have bills to pay, and we all want to go home at a reasonable time and enjoy what's still available of our days." The SQL Cookbook moves quickly from problem to solution, saving you time each step of the way.

Posted By

rengber on 03/24/08


Tagged

sql tsql recursive CTE


Versions (?)


TSQL for Recursive Select Using Common Table Expressions

Published in: SQL 

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

Plain TextExpand 
  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

Report this snippet 

You need to login to post a comment.