Question
Example Table
+-------------+----------------------+--------+
| category_id | name | parent |
+-------------+----------------------+--------+
| 1 | ELECTRONICS | NULL |
| 2 | TELEVISIONS | 1 |
| 3 | TUBE | 2 |
| 4 | LCD | 2 |
| 5 | PLASMA | 2 |
| 6 | PORTABLE ELECTRONICS | 1 |
| 7 | MP3 PLAYERS | 6 |
| 8 | FLASH | 7 |
| 9 | CD PLAYERS | 6 |
| 10 | 2 WAY RADIOS | 6 |
+-------------+----------------------+--------+
Given the example table above, I’m thinking in Oracle I should be able to write SQL along the lines of “SELECT … CONNECT BY” to find the level of a given id. For example, “MP3 Players” has a LEVEL of 3.
Example borrowed from Managing Hierarchical Data in MySQL
Solution
SELECT MAX(level) "LEVEL"
FROM TEST_TABLE
START WITH category_id = 7 -- MP3 Players category_id
CONNECT BY category_id = PRIOR parent
ORDER BY LEVEL DESC
,
You can use level
pseudocolumn to get the “depth” of the current row. Consider the following statement:
SELECT category_id, name, level,
PRIOR name as parent_category
FROM test_table
START WITH parent is null
CONNECT BY PRIOR category_id = parent