integer id column. need to add data

how to link

categories to products

I have a products table
with an integer id column


product 1 could be
category: electronics

product 2
category: electronics > audio > audio components
LVL 1
rgb192Asked:
Who is Participating?
 
sachitjainCommented:
Your category table should be a self referential table like this
create table Category (CategoryId int primary key, CategoryName varchar(50), ParentCategoryId int references Category(CategoryId))

So your values in Category table could be like this
CategoryId      CategoryName            ParentCategoryId
1                  Electronics            Null
2                  Audio                  1
3                  Audio comp.            2
4                  Chipsets                  1

Later you could have your Product table like
create table Product (ProductId int primary key, ProductName varchar(50), CategoryId int references Category(CategoryId))

Thus you could join 2 tables on CategoryId column through following query and get desired results
;with CTE (CategoryId, CategoryName) as
(
      select CategoryId, cast(CategoryName as varchar(150)) from Category where ParentCategoryId is null
      union all
      select c.CategoryId, cast((CTE.CategoryName + '->' + C.CategoryName) as varchar(150))
      from Category C inner join CTE on CTE.CategoryId = C.ParentCategoryId
)
select P.ProductId, P.ProductName, c1.CategoryName
from Product P inner join CTE c1 on P.CategoryId = c1.CategoryId
0
 
Anthony PerkinsCommented:
Care to post the schema (CREATE TABLE) for both tables?
0
 
rgb192Author Commented:
I need to create categories table


products table is integer for productID which is primary key
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
rgb192Author Commented:
why is category.categoryid
not
autoincrement
0
 
sachitjainCommented:
You could keep it autoincrement but since it has to be a self referential foreign key so you would need to take care when you enter ParentCategoryId of other rows in this table. Like suppose CategoryId of Electronics is 1 and that of Audio is 2 comes out through autoincrement then you would have to ensure that ParentCategoryId of Audio is kept as 1 and that of Audio Components is kept as 2 and so on to maintain the hierarchy.
0
 
Anthony PerkinsCommented:
I need to create categories table

products table is integer for productID which is primary key

I am afraid that tells me nothing, so I am glad you got some help.

Good luck.
0
 
rgb192Author Commented:
thanks

i will leave no increment now
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.