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