Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
namitjung
Starting Member
10 Posts |
 Posted - 2006-06-26 : 08:42:08
|
| Hi all,I am developing an ecommerce site on which category hireachy is infinitive.I mean, under one category many subcategories can be created and under that subcategory other subcategories can be created. From the admin side product can be added and it is can be assigned to any available category..I have problem with computing the number of products which exists in one category.Since there are many child categories it should compute the number of products exists on that category too...I would like to explain it as follows: category which has parent id 0 is the root categoryCategory:categoryid categoryname parentid1 A 02 B 03 Child A 14 Child B 35 Child C 4So category hireachy will beA>>Child A>>Child B>>Child CBProductsproductid Product Name Categoryid1 PA 12 PB 33 PC 44 PD 5Here product "PA" exists under "A" category, PB in "Child A",PC in "Child B" and PD in "Child C"Now if we have to calculate the total products from Category A it should return 4, since other three categories are child category of this category....and each category holds the one product.I tried to use join,but since category hireachy is not known i couldn't able to use that..Any comments,advice is greatly Appriciated.Thanx in advance |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-06-26 : 09:00:55
|
| Given that structure where you want no limit to the hierarchy I would maintain aggregate tables for this otherwise you will probably end up with poor performance (I assume that you will want this value returned very often compared to adding/removing entries).Maybe keep a count of the sum of the child entries with each hierarchy entry - this will slow down inserts but hopefully not by much, you could do it via a job which makes the updates after the insert, will be a few seconds later but....Another way is to keep the full path with each entry so that the value becomesselect count(*) from tbl where path like '%\' + @id + '\' (that could cause problems if the site gets large).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
namitjung
Starting Member
10 Posts |
Posted - 2006-06-26 : 09:18:15
|
| Isn't it there anyway except creating new table or inserting full path to solve this problem. |
|
|
|
|
|
|
Computing the aggreate value