All geek questions in one place

Recursive selection of categories

My database has the following table:

Category: {[Name: VarChar, TopCategory: VarChar]}

The table has the following contents:

Contents of the Table

Now I need to get all the names from all subcategories of the Computer Science category using with-clause in a recursive expression. This should be done using SQL, without PHP or another programming language.

All subcategories mean not only direct decoders, but also in this case C++ and Java How to do this?

What I have so far:

 SELECT name FROM category WHERE (topcategory = 'Computer Science')
+4
source share
2 answers

Here we go:

 WITH RECURSIVE cte_t1 (name, topcategory, Level) AS ( SELECT name, topcategory, 0 AS Level FROM Category WHERE topcategory = N'ComputerScience' UNION ALL SELECT t1.name, t1.topcategory, Level + 1 FROM Category t1, cte_t1 ctet1 WHERE ctet1.name= t1.topcategory ) SELECT Level, topcategory, name FROM cte_t1
+4
source

If your oracle database, you can try it ( sql script ):

 select * from category start with name = 'Computer Science' connect by prior name = top_category
0
source

Source: https://habr.com/ru/post/1436753/

More articles:


All Articles