2

This is a category table,

CREATE TABLE `categories` (
  `id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `parentid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This is how you get Category and related Sub-Category.

SELECT root.name  AS root_name
     , down1.name AS down1_name
     , down2.name AS down2_name
  FROM categories AS root
LEFT OUTER
  JOIN categories AS down1
    ON down1.parentid = root.id
LEFT OUTER
  JOIN categories AS down2
    ON down2.parentid = down1.id

 WHERE root.parentid IS NULL
ORDER 
    BY root_name 
     , down1_name 
     , down2_name

SQLfiddle

What I have noticed is that this query goes only 2 step/nodes forward, like say

category > sub-category 1 > sub-category 2

What if I have a sub-category that goes beyond 2 step/nodes like say

category > sub-category 1 > sub-category 2 > sub-category 3

or even may be sub-category 4 do I need to add down3.name or down4.name to get to the end to include all sub-category or is there a better way?

Mecom
  • 381
  • 3
  • 20
  • You can't really in MySQL. You have two choices: change the representation of the hierarchy or use a recursive stored procedure. – Gordon Linoff Jul 01 '17 at 18:09
  • Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) – krokodilko Jul 01 '17 at 19:04

1 Answers1

1

You can get the complete hierarchy of an item using a recursive query.

ex:

    select c.id,c.name,@parent := c.parentid 
from 
 (select * from categories order by id desc) c
join 
 (select @parent := 16) t
where c.id =@parent

create a stored procedure to obtain the desired output for all item ids recursively.

jithin joseph
  • 176
  • 1
  • 6