Select a table that simulates a tree

Question

I have a table that simulates a tree. For example:

DECLARE @t TABLE(id int,parentId int,name varchar(max));
insert @t select 1,  0        ,'Category1'
insert @t select 2,  0,        'Category2'
insert @t select 3,  1,        'Category3'
insert @t select 4 , 2,        'Category4'
insert @t select 5 , 1,        'Category5'
insert @t select 6 , 2,        'Category6'
insert @t select 7 , 3,        'Category7'

My goal is to take each PARENT (idparent=0) and for each branch that this parent has to sequentially list all children up to the lowest level. For that I used this code

WITH tree (id, parentid, level, name , rn) as 
(
   SELECT id, parentid, 0 as level, name,
       right(row_number() over (order by id),10) rn
   FROM @t
   WHERE parentid = 0

   UNION ALL

   SELECT c2.id, c2.parentid, tree.level + 1,  tree.name + ' - ' +c2.name,
       rn 
   FROM @t c2 
     INNER JOIN tree ON tree.id = c2.parentid
)

By selecting Tree I get this back:

id parentid level name                               rn
1  0        0     Category1                          1
2  0        0     Category2                          2
4  2        1     Category2 - Category4              2
6  2        1     Category2 - Category6              2
3  1        1     Category1 - Category3              1
5  1        1     Category1 - Category5              1
7  3        2     Category1 - Category3 - Category7  1

My doubt now arises as to how to get what I really want. From this select I just need the lines with IDS 7, 6 ,4 and 6. That is to say for each RN I always want the lower levels. But I don’t know how to get these values. Can anyone help me? (I’m using SQL Server)

id parentid name                              
4  2        Category2 - Category4             
6  2        Category2 - Category6             
5  1        Category1 - Category5             
7  3        Category1 - Category3 - Category7

Answer 1

I believe if you add one

NOT EXISTS (SELECT 1 FROM @t c3 where c3.parentid = c2.id)

in

WITH tree (id, parentid, level, name , rn) as 
(
   SELECT id, parentid, 0 as level, name,
       right(row_number() over (order by id),10) rn
   FROM @t
   WHERE parentid = 0

   UNION ALL

   SELECT c2.id, c2.parentid, tree.level + 1,  tree.name + ' - ' +c2.name,
       rn 
   FROM @t c2 
     INNER JOIN tree ON tree.id = c2.parentid
   WHERE
     NOT EXISTS (SELECT 1 FROM @t c3 where c3.parentid = c2.id)
)

Answer 2

From what I understand you that only those who have levels return. If that’s it just add AND c2.level > 0 in consultation.

WITH tree (id, parentid, level, name , rn) as 
(
SELECT id, parentid, 0 as level, name,
   right(row_number() over (order by id),10) rn
FROM @t
WHERE parentid = 0

UNION ALL

SELECT c2.id, c2.parentid, tree.level + 1,  tree.name + ' - ' +c2.name,
   rn 
FROM @t c2 
   INNER JOIN tree ON tree.id = c2.parentid AND c2.level > 0
)