Parent To Last Grandchild - MS SQL Recursion Query

Eureka!

This post aims to record my latest sql construction feat.

This is all about sql construction for recursion query using ms sql database.


Consider this table:

PARENT | CHILD
-------+-------
   0   |   1
   1   |   2
   2   |   3
   1   |   4
   4   |   5
   5   |   6
   2   |   7
   6   |   8

As you can see, this table has a parent - child relationship.
What I want to get is to return the last child (grandchild) on the link using the given Child ID.

For example, If i use the Child ID number 1, the query should return 3,7,8.
The chains are shown below:

1 > 2 > 3
1 > 2 > 7
1 > 4 > 5 > 6 > 8

To achive this, here is the recursion query for ms sql database:

;WITH CTE(child, parent)
AS
(
      SELECT  child, parent
    FROM tbl_main WHERE parent = 1
      UNION ALL
      SELECT A.child, A.parent
   FROM tbl_main A INNER JOIN CTE B
   ON B.child = A.parent
)


SELECT T1.child
 FROM CTE T1 LEFT OUTER JOIN CTE T2
   ON T1.child = T2.parent
    WHERE T2.parent IS NULL

(Note: replace tbl_main with your table name)

This query perfectly returns what I need.

I just hope that my Google may index this post for my fellow programmers who would have the same problem in the future.

Happy coding!