Get Child and Parent from hierarchy Table using Common Table Expression (CTE) - SQL 2005 and above

hi, just want to post something that might be useful

DECLARE @a AS TABLE

(

autoid INT 

, name VARCHAR(20)

, parentid INT

)

INSERT INTO @a (autoid, name, parentid)

SELECT 1, '1', 0

UNION ALL

SELECT 2, '1.1', 1

UNION ALL

SELECT 3, '1.2', 1

UNION ALL

SELECT 4, '1.2.1', 3

UNION ALL

SELECT 5, '2.1', 2

UNION ALL

SELECT 6, '3.1', 3

UNION ALL

SELECT 7, '3.2', 3

/*

1 1 0

2 1.1 1

3 1.2 1

4 1.2.1 3

5 2.1 2

6 3.1 3

7 3.2 3

*/

/*

Get Parent Records from child '1.2.1'

return:

1.2 (parent from '1.2.1')

1 (parent from '1.2')

*/

;WITH    employeeMaster

      AS ( SELECT   p.autoid ,

                    p.parentid ,

                    p.[name]

           FROM     @a p

           WHERE    p.[NAME] = '1.2.1'

           UNION ALL

           SELECT   c.autoid ,

                    c.parentid ,

                    c.[name]

           FROM     employeeMaster cte

                    INNER JOIN @a c ON c.autoid = cte.parentid

         )

SELECT  *

FROM    employeeMaster m

WHERE m.[name] <>  '1.2.1'

/*

Get Child Records from parent '1.2'

return: 

1.2.1

3.1

3.2

*/

;WITH    employeeMaster

      AS ( SELECT   p.autoid ,

                    p.parentid ,

                    p.[name]

           FROM     @a p

           WHERE    p.[NAME] = '1.2'

           UNION ALL

           SELECT   c.autoid ,

                    c.parentid ,

                    c.[name]

           FROM     employeeMaster cte

                    INNER JOIN @a c ON c.parentid = cte.autoid

         )

SELECT  *

FROM    employeeMaster m

WHERE m.[name] <>  '1.2'

see u