sql query tree like structure

Question

I have a tree like structure of Categories, the leaf Nodes in the tree have Products and the products have Cods I need to select all the top level Categories (parent=null) that have leafs (Cods) that match some critaria...

SELECT  
    Category.Id AS Id0_, 
    Category.Name AS Name0_, 
    Category.COrder AS COrder0_, 
    Category.Description AS Descript4_0_, 
    Category.ParentId AS ParentId0_, 
    Category.Description_En AS Descript6_0_, 
    Category.Name_En AS Name_En0_, 
    Category.ImagePath AS ImagePath0_ 
FROM
    Category 
    LEFT JOIN Category AS c1 ON Category.Id=c1.ParentId
    LEFT JOIN Category AS c2 ON c1.Id=c2.ParentId
    LEFT JOIN Category AS c3 ON c2.Id=c3.ParentId
    LEFT JOIN Category AS c4 ON c3.Id=c4.ParentId
    LEFT JOIN Product ON 
     c4.Id=Product.Category 
     OR c3.Id=Product.Category 
     OR c2.Id=Product.Category 
     OR c1.Id=Product.Category 
     OR Category.Id=Product.Category
    INNER JOIN Cod ON Cod.Product=Product.Id   
WHERE
    Category.ParentId is null 
    AND Cod.Hidden!='1' 
    AND 
    (
     cod.Stock>0 
     OR (cod.CodBare='0' AND Product.ProdType=8)) 
     AND Cod.Price>0
    )
ORDER BY Category.COrder

my query looks like this, but it is not a solution because it is very very slow... Can someone give me a suggestion on how to do this?

Answer 1

This is a common challenge. Creating hierarchical data from a relational database isn't always elegant. If this data isn't updated constantly, one option is to spit it out as XML and cache that for the application.

If you want to keep it in the DB, this is a common solution:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Answer 2

See the accepted answer to my question http://stackoverflow.com/questions/1757370/recursive-same-table-query-in-sql-server-2008

Answer 3

There are various solutions to represent trees in databases. I really recommend you to take a look to this presentation, Trees in the database. I usually work with a database with multiple levels and 1.5 millions leafs and the nested interval model was really illuminating.

Answer 4

I don't know if you are stuck with this data structure or not but you should really look at this article http://dev.mysql.com/tech-resources/articles/hierarchical-data.html for hierarchical data management.

I have used the solution described here (in oracle and ms-sql) and it is very fast.

Answer 5

See these two examples with recursive CTEs: first, second.