abdujalil  chuliev

abdujalil chuliev

  • 1.8k
  • 368
  • 34k

Select with parent in sql using level, right and left ranges

Nov 2 2022 6:53 AM

I have this:

N_LEVEL saves parent_child relations level, N_LEFT and N_RIGHT columns are ranges of parent_child relations.

How can I select data with hierarchy as shown below:

--create table roles ( id int not null, parentId int, roleName varchar(50) not null );

DECLARE @roles TABLE(id int not null,

    N_LEFT int,

    N_RIGHT int,

    N_LEVEL int,

    DISPLAY_NAME varchar(50))

 

insert into @roles

    (id, N_LEFT, N_RIGHT,N_LEVEL,DISPLAY_NAME)

values

    (1, 97 , 120 , 1 , 'Students'),

    (2, 98 , 113 , 2 , 'Bachelors'),

    (3, 114 , 115 , 2 , 'Masters'),

    (4, 105 , 106 , 3 , '2020' ),

    (5, 99 , 102 , 3 , '2018'),

    (6, 118 , 119 , 2 , 'PhD-DSc'),

    (7, 103 , 104 , 3 , '2019'),

    (8, 116 , 117 , 2 , 'TKDorm'),

    (9, 107 , 108 , 3 , '2021'),

    (10, 109 , 110 , 3 , '2021Add'),

    (11, 111 , 112 , 3 , '2022')

 

select * from @roles


Answers (1)