Show Childs inside the Parents and sub-Childs inside their parent child using
recursive query in SQL Server.
We have a table which contain following data…. Table= tbl_folder
![img1.jpg]()
Now We have write the SQL server Query to show Childs inside the Parents and
sub-Childs inside their parent child
Query
WITH
CategoryList
AS
(
SELECT
parent.FolderId,
CONVERT(VARCHAR(50),
Parent.FolderName)
as FolderName,
parent.ParentfolderID
FROM
tbl _folder as parent
WHERE
parent.ParentfolderID =0
UNION
ALL
SELECT
child.Folderid,
CONVERT(VARCHAR(50),
CL.FolderName +
' / ' + child.FolderName)
as FolderName,
child.ParentfolderID
FROM
tbl_folder as child
INNER
JOIN CategoryList as
CL ON child.ParentfolderID
= CL.FolderId
WHERE
child.ParentfolderID !=0
)
SELECT
*
FROM
CategoryList
After running above query the result will show as given:
Result
![img2.jpg]()
In the above example CategoryList is a Common Expression Table, the base record
is derived by the first sql query before UNION ALL.
Second query after UNION ALL is executed repeatedly to get results and it
will continue until it returns no rows. For above e.g. Result will have
ParenfFolderId which have FolderId (ie, FolderId of the first result). This is
obtained by joining table on columns FolderId with ParentFolderId of table
Tbl_Folder.
WITH provides a way to write subqueries for use in a larger SELECT query. The
subqueries, which are often referred to as Common Table Expressions or CTEs, can
be thought of as defining temporary tables that exist just for this query. One
use of this feature is to break down complicated queries into simpler parts
The general form of a recursive WITH query is always a non-recursive term,
then UNION (or UNION ALL), then a recursive term, where only the recursive term
can contain a reference to the query's own output. Such a query is executed as
follows:
Recursive Query Evaluation
- Evaluate the non-recursive term. For UNION (but not UNION ALL), discard
duplicate rows. Include all remaining rows in the result of the recursive
query, and also place them in a temporary working table.
- So long as the working table is not empty, repeat these steps:
- Evaluate the recursive term, substituting the current contents of the
working table for the recursive self-reference. For UNION (but not UNION
ALL), discard duplicate rows and rows that duplicate any previous result
row. Include all remaining rows in the result of the recursive query, and
also place them in a temporaryintermediate table.
- Replace the contents of the working table with the contents of the
intermediate table, then empty the intermediate table.
A useful property of WITH queries is that they are evaluated only once per
execution of the parent query, even if they are referred to more than once by
the parent query or sibling WITH queries. Thus, expensive calculations that are
needed in multiple places can be placed within a WITH query to avoid redundant
work. Another possible application is to prevent unwanted multiple evaluations
of functions with side-effects. However, the other side of this coin is that the
optimizer is less able to push restrictions from the parent query down into
a WITH query than an ordinary sub-query. The WITH query will generally be
evaluated as stated, without suppression of rows that the parent query might
discard afterwards