Pull all subcategories under one main category, multiple levels of nesting

Question

Using a mysql setup with the following tables:

categories | CATEGORY_ID
categories_sub | CATEGORY_ID | SUB_CATEGORY_ID

In practical terms, the menus output nesting like this:

Products
   Desks
      Tables
      Workstations
   Chairs

Which would be represented by the following SQL entries in categories_sub, assuming appropriate records in categories:

desks01 | products01
   tables01 | desks01
   workstations01 | desks01
chairs01 | products01

All of that being said, is it possible to create a single MySQL query that pulls all of the children of products01 no matter how far nested they are under sub, sub-sub-categories, sub-sub-sub-categories? If so, how would it be written?

I should note that these are not my actual IDs, but simplified ones to make explaining easier. The actual IDs are (sigh) 13-character alphanumeric UNIQID() strings leftover from the previous developer. — , Dec 15 '11 at 16:27
Several examples already exist on Stack: http://stackoverflow.com/questions/5689635/need-help-with-hierarchical-mysql-query to name one. The long and short of it is mySQL doesn't support hierarchical queries, so you'll have to write something. in Oracle they use connect by prior. SQL server DB2 use http://stackoverflow.com/questions/959804/simulation-of-connect-by-prior-of-oracle-in-sql-server (with) — xQbert, Dec 15 '11 at 16:35
You can also use the `with` approach in recent postgres, which is cheaper than buying a commercial RDBMS. — sorpigal, Dec 15 '11 at 17:18

score 0 · Answer 1 · answered Jan 07 '12 at 14:00

Change your structure to a single table with the following cols

id | category | parent

use the id of the upper level category as the value for the parent and then have a look at recursive functions to retrieve the structure. If its a Root or Top Level category use '0' as the parent value.

Remember that recursive function are a possible infinite loop trap - always exit them at the first possible point! Im not going to write the function for you but to help you along - a recursive function calls itself!

Pull all subcategories under one main category, multiple levels of nesting

1 Answers1