sql recursive query

Lets say you have a Table in MS SQL 2005 like this...

CatCode (int)
CatParent (int)
CatName (varchar (100) )

What you want to do is retrieve all the children and sub children of a parent category...something like this......

Cookware
      Microwaves
           CounterTop Microwaves
           Built-in Microwaves

You can do this with one query in MS  Sql / T-SQL using CTE, below is the Query on how to do it.

With myCats
As
(

 Select Catparent,Catname, Catcode, 0 as [level] From CategoryRelationShip C  where C.CatParent = 27
 Union All
 Select Cm.CatParent,cm.Catname, Cm.Catcode,level=[level] + 1 From CategoryRelationShip Cm
 Inner join myCats m on Cm.CatParent = m.CatCode

)

Select  * From myCats

As you can see, first query simply asks what is your main query (or which parents you want to return). and second query basicly joins to it untill no rows are return. simple and magical query.

enjoy

Posted by:

No comments posted.

Name :  

Email :  

Comment Below