sql server - Sql query for 3 level tree? -

i have table , want 3 lvl tree. example

node id     parentid     name  1       -1       test 1  2       -1       test 2  3        1       test 1.1  4        1       test 1.2  5        3       test 1.1.1  6        3       test 1.1.2  7        5       test 1.1.1.1 

if filtered parentid = -1 want rows parentid = -1 , children's +2 lvl.

if filtered id = 2 want row id = 2 , children's +2 lvl.

update

i use ms sql server 2008, entity framework 6.1.3. understand, can use 3 selects. looking effective method

you can use recursive sql in sql server.

with reccte (childid, parentid, name, depth) assuming (     select         yourtable.id childid,         yourtable.parentid,         cast('test ' + yourtable.id varchar(20)) name         0 depth             yourtable             parentid = -1      union      select         yourtable.id childid,         yourtable.parentid parentid,         reccte.path + '.' + yourtable.id name         reccte.depth + 1 depth             reccte         inner join yourtable on         reccte.childid = yourtable.parentid             reccte.depth + 1 <= 2 )  select * reccte; 

the bit inside cte on top of union seed query recursive sql. it's place recursive lookup start. wanted start @ parentid = -1, it's here in where statement.

the bit inside cte below union recursive term. joins recursive cte , brings in more data table. joining id table childid recursive resultset.

the recursive term test see how deep we've gotten. if depth cte + 1 less or equal 2 stop adding children loop up, ending loop particular leg of hierarchy.

the last little bit below cte part runs cte results back.

these recursive queries confusing hell @ first, spend time them , you'll find lot of use them. you'll find aren't difficult write once have parts sussed out.