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?

The database is MSSQL 2005 about Cods ... the products are more like Product types that have many variations (like color size etc.) and the Code is the actual product the one with a serialnumber/product code — bogdanbrudiu, Nov 19 '09 at 18:57
See these two examples with recursive CTEs: [**first**](http://stackoverflow.com/questions/1760681/sql-recursive-menu-sorting/1763675#1763675), [**second**](http://stackoverflow.com/questions/1757370/recursive-same-table-query-in-sql-server-2008/1758797#1758797). — Damir Sudarevic, Nov 19 '09 at 22:19
See the accepted answer to my question http://stackoverflow.com/questions/1757370/recursive-same-table-query-in-sql-server-2008 — CesarGon, Nov 19 '09 at 18:56

score 5 · Accepted Answer · edited Aug 21 '11 at 22:38

5

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: Managing Hierarchical Data in MySQL.

edited Aug 21 '11 at 22:38

Paŭlo Ebermann

73,284
20
146
210

answered Nov 19 '09 at 18:51

DA.

39,848
49
150
213

@DA, take a look at the presentation I've posted in my solution. If you like the the Nested Set Model you'd enjoy even more the "improvement", the Nested Interval Model. – Sergi Nov 19 '09 at 18:59
@Sergi, I don't get to play in SQL as much as I used to, but always up for learning something new. Will take a look! – DA. Nov 19 '09 at 19:03
the article is pertinent but not a solution because the structure changes quite offen and the application will need a total rewrite... my bad I did not think it will get to this... – bogdanbrudiu Nov 19 '09 at 19:03

score 0 · Answer 2 · answered Nov 19 '09 at 18:56

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.

score 0 · Answer 3 · edited Aug 21 '11 at 22:40

0

I don't know if you are stuck with this data structure or not but you should really look at the article Managing Hierarchical Data in MySQL for hierarchical data management.

I have used the solution described here (in Oracle and MS SQL Server) and it is very fast.

edited Aug 21 '11 at 22:40

Paŭlo Ebermann

73,284
20
146
210

answered Nov 19 '09 at 18:57

lofte

864
7
6

sql query tree like structure

3 Answers3