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

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)

Answer 2

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