Need a help in query?

Question

I have a hierarchical tree table structure .How can i get the left tree and right tree .

1   a   NULL
2   b   1
3   c   1
4   d   2
5   e   2
6   f   3
7   g   3
8   h   4
9   i   4
10  j   5
11  k   5
12  l   6 

If i have the id of a ie 1 .how can i get the tree of b and c

i am expecting the tree under b as

2   b   
4   d   
5   e   
8   h   
9   i   
10  j   
11  k   

Here i an following adjacency model only i am going .I am not looking for nested model. This mit be a duplicate question .

Answer 1

I think what you need are CTE (Common Table Expressions)

This kind of recursive CTE can be found here, and try this for general help with CTE's as they tend to use less-than-standard SQL syntax

Answer 2

this will return the tree structure for parent 1

DECLARE @Table TABLE(
        ID INT,
        Val VARCHAR(MAX),
        ParentID INT
)

INSERT INTO @Table (ID,Val,ParentID) SELECT 1, 'a', NULL
INSERT INTO @Table (ID,Val,ParentID) SELECT 2, 'b', 1
INSERT INTO @Table (ID,Val,ParentID) SELECT 3, 'c', 1
INSERT INTO @Table (ID,Val,ParentID) SELECT 4, 'd', 2
INSERT INTO @Table (ID,Val,ParentID) SELECT 5, 'e', 2
INSERT INTO @Table (ID,Val,ParentID) SELECT 6, 'f', 3
INSERT INTO @Table (ID,Val,ParentID) SELECT 7, 'g', 3
INSERT INTO @Table (ID,Val,ParentID) SELECT 8, 'h', 4
INSERT INTO @Table (ID,Val,ParentID) SELECT 9, 'i', 4
INSERT INTO @Table (ID,Val,ParentID) SELECT 10, 'j', 5
INSERT INTO @Table (ID,Val,ParentID) SELECT 11, 'k', 5
INSERT INTO @Table (ID,Val,ParentID) SELECT 12, 'l', 6

DECLARE @ParentID INT

SET @ParentID = 1

;WITH TreeSelect AS(
        SELECT  ID,
                Val
        FROM    @Table
        WHERE   ParentID = @ParentID
        UNION ALL
        SELECT  t.ID,
                t.Val
        FROM    @Table t INNER JOIN
                TreeSelect ts ON t.ParentID = ts.ID
)
SELECT  *
FROM    TreeSelect
OPTION (MAXRECURSION 0)