Advanced Search

Managing Hierarchical Data in MySQL

/ Published in: MySQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. Retrieving a Full Tree
  2.  
  3. The first common task when dealing with hierarchical data is the display of the entire tree, usually with some form of indentation. The most common way of doing this is in pure SQL is through the use of a self-join:
  4.  
  5.  
  6. SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
  7. FROM category AS t1
  8. LEFT JOIN category AS t2 ON t2.parent = t1.category_id
  9. LEFT JOIN category AS t3 ON t3.parent = t2.category_id
  10. LEFT JOIN category AS t4 ON t4.parent = t3.category_id
  11. WHERE t1.name = 'ELECTRONICS';
  12.  
  13.  
  14. TABLE STRUCTURE AS AN EXAMPLE 
  15. ===============================
  16.  
  17. CREATE TABLE category(
  18. category_id INT AUTO_INCREMENT PRIMARY KEY,
  19. name VARCHAR(20) NOT NULL,
  20. parent INT DEFAULT NULL);
  21.  
  22.  
  23. INSERT INTO category
  24. VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
  25. (4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),
  26. (7,'MP3 PLAYERS',6),(8,'FLASH',7),
  27. (9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.