selecting from deeper levels - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

selecting from deeper levels

Author

Topic

sujithukvl@gmail.com
Starting Member

22 Posts

Posted - 2007-11-27 : 07:47:51

selecting number of products that comes under a parent category

sqlserver2005

CREATE TABLE [ProductCategoryAssociation](
[CategoryID] [bigint] , --this is primary key of table Category
[ProductID] [bigint] ,--this is primary key of table product
)

the above table binds a product to its category

CEATE TABLE [Category](
[CategoryID] [bigint] IDENTITY(1,1) -- this is primary key of table category

[Name] [nvarchar](255)

[ParentCategory] [bigint] NULL,
)

I have a Category"A" as a supper parentcategory

I have 1 (one)product in Category"A".

Category"A" have 2 child category A11,A12.

A11,A12 both have 2 products in the category (totally 4)

Now I can get the count of all product belong Category"A" (ie is 5= 1+2*2) five

let 1 be the id of category "A"

Now the following querry give me the expected result

select count(ProductId) from ProductCategoryAssociation where categoryid=1 or categoryid in (select categoryid from Category where Parentcategory=1)

BUT if

i have child for A11 named A111 and if there is ONE product belong to A111 this querry wont take that in to cosideration

so

the following querry will do it

select count(ProductId) from ProductCategoryAssociation where categoryid=1 or categoryid in (select categoryid from Category where Parentcategory=1 or Parentcategory in (select categoryid from Category where Parentcategory=1))

ie i nested one more select

but this has limitations

So kindly give me a general solution to check up to the las level of catego

nr
SQLTeam MVY

12543 Posts

Posted - 2007-11-27 : 08:07:40

Have a look at CTEs. There is an example in bol of what you are looking for.

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

sujithukvl@gmail.com
Starting Member

22 Posts

Posted - 2007-11-27 : 08:13:36

where is the example
i am just a beginer in sql

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-27 : 08:37:27

http://www.4guysfromrolla.com/webtech/071906-1.shtml

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources