Anonymous One Anonymous One - 6 months ago 15
SQL Question

How to recursively populate sub-categories?

I've a table named

categories
.

The table structure is as follows:

DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) CHARACTER SET utf8 NOT NULL,
`parent_id` int(11) NOT NULL DEFAULT '-1',
PRIMARY KEY (`ID`)
);


There are 3 types of items in the table:


  1. Main Category (whose
    parent_id
    is -1)

  2. Second Level Category (whose
    parent_id
    is any of the Main categories)

  3. Third level category (whose
    parent_id
    is any of the Second Level Categories)



Here's some sample data:




-- Records of categories




INSERT INTO `categories` VALUES ('1', 'A', '-1');
INSERT INTO `categories` VALUES ('2', 'B', '-1');
INSERT INTO `categories` VALUES ('3', 'C', '-1');
INSERT INTO `categories` VALUES ('4', 'a', '1');
INSERT INTO `categories` VALUES ('5', 'b', '2');
INSERT INTO `categories` VALUES ('6', 'c', '3');
INSERT INTO `categories` VALUES ('7', 'aa', '4');
INSERT INTO `categories` VALUES ('8', 'bb', '5');
INSERT INTO `categories` VALUES ('9', 'cc', '6');
INSERT INTO `categories` VALUES ('10', 'ccc', '6');


I want to get an output structure like below:

Main Category Second Level Category Third Level Category
A a aa
B b bb
C c cc
C c ccc


Any help is appreciated.

Answer

You can give it a try:

SELECT 
t1.title AS 'Main Category',
t2.title AS 'Second Level Category',
t3.title AS 'Third Level Category'
FROM categories AS t1
INNER JOIN categories AS t2 ON t2.parent_id = t1.ID
INNER JOIN categories AS t3 ON t3.parent_id = t2.ID;

You will get output structure like below:

Main Category      Second Level Category        Third Level Category
A                           a                           aa
B                           b                           bb
C                           c                           cc
C                           c                           ccc

Reference