Sql Self join query? How to get subcategory categories?

Question

I have a database table that looks like this

  catID |  category |  parentID
 1 |  firstCategory |  null
 2 |  SubCategory1 |  1
 3 |  SubCategory2 |  1
 4 |  subSub1 |  3

etc.

There are several levels of categories. Which query can be used to get records in the following format:

  catID |  category 
 1 |  firstCategory
 2 |  firstCategory / SubCategory1
 3 |  firstCategory / SubCategory2
 4 |  firstCategory / SubCategory2 / subSub1

The category identifier will be the identifier of the last category. How to write a query to join categories at all levels? Does the exact number of levels for different categories differ?

I am using mySQL.

Answer 1

For a maximum depth of 6 (including root) you can use this

select l0.catID, concat( case when l5.catID is null then '' else concat(l5.category, '/') end , case when l4.catID is null then '' else concat(l4.category, '/') end , case when l3.catID is null then '' else concat(l3.category, '/') end , case when l2.catID is null then '' else concat(l2.category, '/') end , case when l1.catID is null then '' else concat(l1.category, '/') end , l0.category) from catcat l0 left join catcat l1 on l0.parentID=l1.catID left join catcat l2 on l1.parentID=l2.catID left join catcat l3 on l2.parentID=l3.catID left join catcat l4 on l3.parentID=l4.catID left join catcat l5 on l4.parentID=l5.catID 

Expand the template as required for longer maximum depths.

Answer 2

Oracle has this functionality, and the company I work with uses it exactly for what you describe. However, requests can be quite heavy. A good feature entry ("start with" and "join by keywords") is here at this link, along with a pseudocode that you can try to wrap around ... although cyberkiwi's answer is probably just good for all practical purposes ...

http://www.adp-gmbh.ch/ora/sql/connect_by.html

Answer 3

There is an alternative to what cyberkiwi said: querying the entire table and tree in memory. Imperative languages ​​are good for this, but SQL is not. Performance will be much better (because SQL must scan the table not only once, but for each level).