Recursive Database Query

Hi, it is quite a long time I am not writing any technical blog. One important reason is I am out of technical stuffs for long time. And another one is I am not learning any new things. I repeat some boring works regularly.

Whatever, I encounter very interesting thing. At least I found it interesting to me. Recursive Database query. Things are like; say you have a content table in database called ItemContent. And your content is hierarchical. That means some content have parent child relationship among them. And you got a separate table called ItemHierarchy. The relationship is defined in this table. Now you need all the descendant of any item those meets your requirement or May you need descendant required until a specific level. Here I show an interesting recursive query for these sorts of phenomena. This is highly Microsoft SQL specific implementation; you may try it on other database system.

WITH RecursiveTable(ID,Depth) AS

(

SELECT ID,1 FROM [ItemContent] as IC WHERE [Name] = 'root' UNION ALL

SELECT childID,RT.Depth+1 from ItemHierarchy HT inner join RecursiveTable RT ON RT.ID = HT.parentID AND T.Depth <5

)

SELECT ID FROM RecursiveTable;

The query is pretty simple, only interesting thing inside it, it is recursive. The first part sort out the specific parent and the rest will recursively find their descendents. That’s it.