Geek asks and answers

SQL for hierarchical relationships

I have a table where products are classified based on hierarchical relationships such as a tree structure. I need to select a category and all subcategories at any level. See the picture below:

enter image description here

eg. I need a sql statement which, when I request the transfer id = 11, returns me (19,20,21,22,23,24,25,26)

+5
source share
4 answers

There are several different ways to store hierarchical data in MySQL. Check out the Bill Karwin presentation , which showcases four options.

  • Contact list
  • Enumeration path
  • Nested Kits
  • Closing table

, , , , .

nested sets query subtree

:

  • .
  • n .
  • . . Quassnoi - MySQL.
+9
SELECT * FROM `Products` 
WHERE parentId IN (
    SELECT id FROM `Products` 
    WHERE parentId = 11)

. , , 2 .

+1

, linage. , ( ).

,

26 \11\

If you have a sub, you could

\11\subitem\

Then you can just do the same check in your linage table, its much faster than iterative search, and you can create it using a stored procedure or triggers.

Node    ParentNode  EmployeeID  Depth   Lineage
100        NULL         1001            0   /
101        100          1002            1   /100/
102        101          1003            2   /100/101/
103        102          1004            3   /100/101/102/
104        102          1005            3   /100/101/102/
105        102          1006            3   /100/101/102/
+1
source

This is messy, and you will need to make n unions, where n is how deep your hierarchy is, but it should work:

SELECT * FROM `Products` WHERE parentId IN (
    SELECT id FROM `Products` WHERE parentId = 11)
UNION
SELECT * FROM `Products` WHERE parentId IN (
    SELECT id FROM `Products` WHERE parentId IN (
         SELECT id FROM `Products` WHERE parentId = 11))
UNION
SELECT * FROM `Products` WHERE parentId IN (
    SELECT id FROM `Products` WHERE parentId IN (
        SELECT id FROM `Products` WHERE parentId IN (
             SELECT id FROM `Products` WHERE parentId = 11)))
0
source

More articles: