Filter Products with Deeply Nested Categories

Transact-SQL
1

Hi all,

I have 2 tables - ProductCategory and Product
Each Product is linked to 1 ProductCategory.

I am trying to filter a list of Product records when I specify a particular ProductCategory. The problem is that the ProductCategory table is a "recursive" table that allows users to group different categories together.

Example
ProductCategory Table

CatID	ParentCatID	Name
1	NULL		Stationery
2	1		Writing Instruments
3	1		Paper
4	2		Pens
5	2		Pencils


ProdID	Name		CatID
100	Ball Point Pen	4
101	B1 Pencil	5
103	A4 Paper	3
104	Erasers		1

If I draw a diagram to represent the above ProdutCategory data it will look like this

1 - Stationery
    2 - Writing Instruments
            4 - Pens
            5 - Pencils
    3 - Paper

So you can see that Writing Instruments and Paper is under Stationery. And Pens and Pencil is under Writing Instruments (which is under Stationery).

Now if I try to filter all Products that are of type Stationery (CatID = 1), I am unable to show any products that are of CatID 4 (Pens) and CatID 5 (Pencils).

This is what I have so far.

SELECT	P.*
FROM	Product P INNER JOIN ProductCategory PC ON P.CatID = PC.CatID
WHERE	PC.CatID = @CatID OR PC.ParentCatID = @CatID

Any help is much appreciated.

2

Try this:

declare @CatID int=1;

with cte(EntryCatID,CatID)
  as (select CatID as EntryCatID
            ,CatID
        from ProductCategory
      union all
      select a.EntryCatID
            ,b.CatID
        from cte as a
             inner join ProductCategory as b
                     on b.ParentCatID=a.CatID
     )
select p.*
  from Product as p
       inner join cte as pc
               on pc.CatID=p.CatID
 where pc.EntryCatID=@CatID