[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

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
0
rgb192
Asked:
rgb192
  • 3
  • 2
  • 2
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now