asked on

Sql query in tree hierarchy

Hi ,

We have 3 tables:
1 Categories (with column CategoryID, ParentID)
2. Products (with ProductID, other products related fields)
3. Category_product (with CategoryID and ProductID)

Category table have mult level hierarchy i.e Category, Sub category, Sub Sub Category.

Now I want single query to display the products with the help of Main CategoryID

For Example
There is one main category with CategoryID =2
There are 3 sub category with IDs 6,7,8
Further each sub category have its own sub sub categories.

Now I want to display all products of categoryId 2 including its sub and sub sub categories.

Please suggest.

Rikin Shah

you can achieve this by recursive query, here are some references-
http://www.mssqltips.com/tip.asp?tip=1520
http://blog.sqlauthority.com/2008/07/28/sql-server-simple-example-of-recursive-cte/

ASKER CERTIFIED SOLUTION

johanntagle

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wdosanjos

Please try the following (SQL Server 2008):

;With CategoryHierarchy(TopCategoryID, CategoryID, ParentID, CategoryLevel) As
(
    Select c.CategoryID TopCategoryID, c.CategoryID, c.ParentID, 0 As CategoryLevel
      From Categories c
     Where c.ParentID = 0
    Union All
    Select pc.TopCategoryID, sc.CategoryID, sc.ParentID, CategoryLevel + 1
      From Categories As sc
           Inner Join CategoryHierarchy As pc
                      On sc.ParentID = pc.CategoryID 
)

Select p.*
  From Products p
       Inner Join Category_Product cp
                  On cp.ProductID = p.ProductID
       Inner Join CategoryHierarchy ch
                  On ch.CategoryID = cp.CategoryID
 Where ch.TopCategoryID = 2

Open in new window

wdosanjos

BTW, it assumes that the top category record has ParentID = 0. If that's not the case, please update Line #5 accordingly.

Gurbirs

ASKER

ggg