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

Answer 1

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.

Answer 2

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

Answer 3

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.