integer id column.  need to add data

Posted on 2012-09-02
Last Modified: 2012-09-04
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
Question by:rgb192
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Care to post the schema (CREATE TABLE) for both tables?

    Author Comment

    I need to create categories table

    products table is integer for productID which is primary key
    LVL 12

    Accepted Solution

    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

    Author Comment

    why is category.categoryid
    LVL 12

    Expert Comment

    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.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    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.

    Author Closing Comment


    i will leave no increment now

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
    Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now