Managing Hierarchical Data in MySQL

MHD.sql

CREATE TABLE category(category_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, parent INT DEFAULT NULL);

INSERT INTO category VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),(4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),(7,'MP3 PLAYERS',6),(8,'FLASH',7),(9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4, t5.name as lev5
FROM category AS t1
INNER JOIN category AS t2 ON t2.parent = t1.category_id
INNER JOIN category AS t3 ON t3.parent = t2.category_id
INNER JOIN category AS t4 ON t4.parent = t3.category_id
INNER JOIN category AS t5 ON t5.parent = t4.category_id
WHERE t1.name = 'ELECTRONICS';


CREATE TABLE nested_category ( category_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, lft INT NOT NULL, rgt INT NOT NULL);

INSERT INTO nested_category VALUES(1,'Clothing', 1, 22),
(2,'Men\'s', 2, 9),
(3,'Women\'s', 10, 21),
(4,'Suits', 3, 8),
(5,'Slacks', 4, 5),
(6,'Jackets', 6, 7),
(7,'Dresses', 11, 16),
(8,'Skirts', 17, 18),
(9,'Blouses', 19, 20),
(10,'Evening Gowns', 12, 13),
(11,'Sun Dresses', 14, 15);

SELECT parent.name FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.name = 'Dresses' ORDER BY parent.lft;

SELECT node.name, (COUNT(parent.name) - 1) AS depth FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt GROUP BY node.name ORDER BY node.lft;