How to locate row within a hierarchy

Question

I'm trying to check if a specific ID can be located within a hierarchy.

I have a list of categories stored in a hierarchical fashion. Example

(0x,       0, 1,  'root'),
(0x58,     1, 2,  'Market Zone'),
(0x5AC0,   2, 3,  'Informática'),
(0x5AD6,   3, 8,  'Accesorios Robótica'),
(0x5ADA,   3, 9,  'Accesorios Eléctricos'),
(0x5ADE,   3, 10, 'Equipos'),
(0x5ADEB0, 4, 12, 'Portátiles'),
(0x5ADED0, 4, 11, 'Accesorios Portátil'),
(0x5ADEF0, 4, 13, 'Máquinas de Oficina'),
(0x5ADF08, 4, 14, 'PC y TPV'),
(0x5ADF18, 4, 15, 'Accesorios PC y TPV'),
(0x5ADF28, 4, 16, 'Servidores'),
(0x5AE1,   3, 17, 'Iluminación'),
(0x5AE3,   3, 18, 'Periféricos - Informática'),
(0x5AE358, 4, 41, 'Cajas Externas')

The items table has a field that links to the category that the item belongs to. What I'm trying to achieve is to list (by level) only the category "branches" that has items.

I hope it's clear enough this time.

Thanks for your help.

P.S.: Forgeive me about the Spanish descriptors, but the application is in Spanish

Could you tell us which database are you using? – Szilard Barany May 17 '12 at 14:59 — Szilard Barany, May 17 '12 at 14:59
MS SQL Server R2 (sorry for missing that) – Carlos May 17 '12 at 16:52 — Carlos, May 17 '12 at 16:52
I'm reformulating the question, perhaps is not clear enough – Carlos May 18 '12 at 08:28 — Carlos, May 18 '12 at 08:28

score 0 · Answer 1 · answered May 17 '12 at 15:01

0

In Oracle you can use the CONNECT_BY_ROOT operator to show only the root elements. You might need to use DISTINCT as well in an outer query.

answered May 17 '12 at 15:01

Szilard Barany

1,125
7
14

score 0 · Answer 2 · answered May 17 '12 at 17:21

Try:

DECLARE @CategoryId INT
SET @CategoryId = 23;

WITH Category_CTE (CategoryId, ParentId, Lvl) AS 
(
    SELECT CategoryId, ParentId, 1 lvl
    FROM Category
    WHERE CategoryId = @CategoryId
UNION ALL
    SELECT c.CategoryId, c.ParentId, Lvl+1 lvl
    FROM Category c
    INNER JOIN Category_CTE cte ON cte.ParentId = c.CategoryId
)
SELECT *
FROM Category_CTE
ORDER BY Lvl DESC

Above query will return result:

CategoryId  ParentId    Lvl
----------- ----------- -----------
0           NULL        4
2           0           3
21          2           2
23          21          1

Category Table Data:

CategoryId  ParentId    CategoryName
----------- ----------- ------------
0           NULL        Category 1
1           0           Category 2
12          1           Category 3
13          1           Category 4
2           0           NULL
21          2           NULL
23          21          NULL

score 0 · Answer 3 · answered May 17 '12 at 18:31

0

According to your example, the following does the trick:

Select left(category, 3)
 From categoryTree
Where right(category, 2) in ('17', '23')

I would make this more general using like or charindex, but I'm on my iPhone. It is a pain to input code.

answered May 17 '12 at 18:31

Gordon Linoff

1,242,037
58
646
786

How to locate row within a hierarchy

3 Answers3