May 21, 2007 at 1:51 pm
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
May 22, 2007 at 7:14 pm
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply