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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Care to post the schema (CREATE TABLE) for both tables?
rgb192Author Commented:
I need to create categories table


products table is integer for productID which is primary key
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

rgb192Author Commented:
why is category.categoryid
not
autoincrement
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.
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.
rgb192Author Commented:
thanks

i will leave no increment now
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.