Posted By

jeffery2k2610 on 09/07/09


Tagged


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

kubaitis
virendrakryadav


Managing Hierarchical Data in MySQL


 / Published in: MySQL
 

  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  

You need to login to post a comment.