How to find out product quantity in a category with its subcategory

• OZEN

  SSC Rookie

  Points: 27

  More actions

  May 21, 2007 at 1:51 pm

  #176671

  Hi everyone,

  I have table named Categories like that;

  CategoryId ---(id filed)    

  LevelId ---(stored Level of the category.)    

  ParentCategoryId  ---(stored the id of Parent category of the subcategory)

  CategoryName   

  CategoryDescription

  And also I have Products table it has CategoryId field related with Category table.

  I want to return a value of quantity of the products for each category with their subcategories

  for example when I give the notebooks id to the storedprocedure as @CategoryID parametre, It must return to me the quantity of the products in both Notebooks category and its subcategories like notebook accesories.

  Any suggestions at a solution gratefully recieved

  Ozen

• Kathi Kellenberger

  SSChampion

  Points: 11811

  More actions

  May 22, 2007 at 7:14 pm

  #708233

  I'll attempt  solution here based on what I think you are trying to say.  By the way, it really helps out everyone if you can supply the create table statements along with sample data and sample results.

  create

  table Categories(categoryID int,levelId int,

  ParentCategoryID

  int, Categoryname varchar(30),

  CategoryDesc

  varchar(30));

   

  insert

  into Categories(categoryID, levelId, ParentCategoryID,

  CategoryName

  , CategoryDesc)

  Select

  1,0,NULL,'Notebooks','Notebooks'

  UNION

  select

  2,0,NULL,'Pencils','Pencils'

  UNION

  select

  3,1,1,'Notebook accessories','Notebook accessories';

  create

  table Products(ProductID int, CategoryID int);

  insert

  into Products(ProductID, CategoryID)

  select

  10,1

  union

  select

  20,1

  union

  select

  30,1

  union

  select

  40,2

  union

  select

  50,2

  union

  select

  60,3

  union

  select

  70,3;

   

   

  select

  count(*) as ProdCount

  from

  Products p join Categories c

  on

  p.CategoryID = c.CategoryID

  where

  c.CategoryID = 1 or c.ParentCategoryID = 1

   

   

  Aunt Kathi Data Platform MVP
  Author of Expert T-SQL Window Functions
  Simple-Talk Editor