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......

           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

 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.


Posted by:

No comments posted.

Name :  

Email :  

Comment Below