CODEWITHSUNDEEP
mysqlsql
Noor
Noor

Reputation: 20178

SQL for hierarchical relationship

I've a table where products are categorised based on hierarchical relationship like a tree structure. I'm having to select a category and all subcategories at any level. See the picture below:

enter image description here

e.g. i want an sql statement that when i query passing the id=11, it returns me (19,20,21,22,23,24,25,26)

Upvotes: 7

Views: 1635

Answers (4)

TeeJay Green
TeeJay Green

Reputation: 36

This is messy, and you'll have to do 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)))

Upvotes: 0

Ozair Kafray
Ozair Kafray

Reputation: 13549

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

NOTE: This won't work if your hierarchy is deeper than 2 levels.

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 839074

There are several different ways to store heirarchical data in MySQL. Check out Bill Karwin's presentation that demonstrates four options.

  • Adjacency List
  • Path Enumeration
  • Nested Sets
  • Closure Table

You are using the adjacency list model for storing heirarchical data, but unfortunately this is the hardest model you could choose for querying subtrees.

nested sets query subtree

Your options are:

  • Change to a different model.
  • Restrict queries to n levels deep.
  • Use a stored procedure to query recursively. For more information about this, see Quassnoi's series of articles - Hierarchical queries in MySQL.

Upvotes: 11

nyxthulhu
nyxthulhu

Reputation: 9762

Could you change your data-structure a little to include a computed linage column. There's a great article which shows you the generic concept (ignore the database type).

Basically your computed linage column should contain the list of parents in it for example

Item 26 would contain \11\

If you had a subitem you could have

\11\subitem\

Then you can simply do a like check on your linage table, its much faster than a iterative search, and you could do create it using a stored proc 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/

Upvotes: 1

Related Questions