Make a select to a table that simulates a tree

Question

Make a select to a table that simulates a tree

Navigation

#1 by (2 votes)
#2 by (0 votes)

4

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 pick up each FAT (idparent = 0), and for each branch that parent has sequentially list all children to the lowest level. For this 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
)

Making a select to tree is returned to me:

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 question now arises as to what I really want. From this select I only need the lines with IDS 7, 6, 4 and 6. In other words, for each RN I always want the lowest levels. But I do not know how to get those values. Can someone 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

sql-server

asked by anonymous 25.04.2014 / 18:43

2 answers

0

So I understood that you only return those who have levels. If so, just add AND c2.level > 0 to the query.

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
)

25.04.2014 / 19:23

How can I get the id in classes with equal names? Doubts object orientation + java ORM

score 2 · Accepted Answer

I believe that 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)
)