|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
November 5th, 2004, 12:34 AM
|
|||
|
|||
|
Category / subcategory
Dear All,
I am doing a auction project and my requirement is I need to show the Main category along with total count of products in it and its subcategories... Electrical & Households (11) Kitchen(5) Dining(6) In the above out put the Electrical & Household is the main category and Kitchen(5) Dining(6) are subcategories.. My table is tbl_cat cid Numeric Identity Parent Numeric Cat_Name Varchar(100) tbl_Procucts pid Numeric Identity Cat_Id Numeric (foreign key of tbl_cat) Prod_Name Varchar(100) For clear idea pls visit http://www.erealdeal.com Pls assist me ... Thanks Bala
|
|
#2
November 5th, 2004, 03:13 AM
|
||||
|
||||
|
if you have more than one level of subcategory within a category, you will need to decide whether you want to continue extending the tree downwards with further left outer joins, or whether to use recursion
here is the solution for one level, i.e. major category and one level of subcategory: Code:
select sum(levelcount)
from (
select count(tbl_Procucts.Cat_Id) as levelcount)
from tbl_cat
left outer
join tbl_Procucts
on tbl_cat.cid
= tbl_Procucts.Cat_Id
where cid = number
union all
select count(tbl_Procucts.Cat_Id)
from tbl_cat cat
inner
join tbl_cat subcat
on cat.cid
= subcat.Parent
left outer
join tbl_Procucts
on subcat.cid
= tbl_Procucts.Cat_Id
where cat.cid = number
) as foo
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Category / subcategory |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
