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.
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.