Saturday, July 29, 2017

MySQL: querying for hierarchical data for set number of levels

Problem: I have a table cherryshoetech that has hierarchical data, and needed to output out the name's of each record by each level (i.e. Level_1, Level_2, etc).


Table Definition:
Columns     Type              Comments
---------       ------------      ----------------------------------------
id                int(11)           primary key
name          varchar(200)
type            varchar(20)
parent_id    int(11)           parent of this record, null if no parent

Without using a stored procedure, and knowing that the hierarchical data can only go up to six levels, the below query works to output the 6 Levels by name:

-- We know we have at most six levels of cherryshoetech so only need to join up to 6 levels
SELECT, AS Level_1, as Level_2, as Level_3, as Level_4, as Level_5, as Level_6
FROM cherryshoetech cst1
LEFT JOIN cherryshoetech AS cst2 ON cst2.parent_id =
LEFT JOIN cherryshoetech AS cst3 ON cst3.parent_id =
LEFT JOIN cherryshoetech AS cst4 ON cst4.parent_id =
LEFT JOIN cherryshoetech AS cst5 ON cst5.parent_id =
LEFT JOIN cherryshoetech AS cst6 ON cst6.parent_id =

This article helped a lot.

No comments:

Post a Comment

I appreciate your time in leaving a comment!