Category product tables

Question

Hi all I have 3 tables CREATE TABLE [dbo].[items]( [itemId] [uniqueidentifier] NOT NULL [itemname] [nvarchar](150) NOT NULL CONSTRAINT [PK_items] PRIMARY KEY CLUSTERED ([itemId] ASC) WITH (PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF IGNORE_DUP_KEY = OFF ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] )ON [PRIMARY] CREATE TABLE [dbo].[itemcategory]( [categoryId] [uniqueidentifier] NOT NULL [itemId] [uniqueidentifier] ) NOT NULL ON [PRIMARY] what I want is: Main categories must be in one dropdownlist subcategories in an another dropdownlist and items in third dropdownlist. I need to prepare stored procedure for this 3 tables. Could you help me please? Thanks Trifon. CREATE TABLE [dbo].[category]( [categoryId] [uniqueidentifier] NOT NULL [catParentId] [uniqueidentifier] NULL [categoryName] [nvarchar](50) NOT NULL CONSTRAINT [PK_category] PRIMARY KEY CLUSTERED ( [categoryId] ASC )WITH (PAD_INDEX = OFF STATISTICS_NORECOMPUTE = OFF IGNORE_DUP_KEY = OFF ALLOW_ROW_LOCKS = ON ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] )ON [PRIMARY]

Trifon Dinev · Answer

Hi Pradeep in the table CREATE TABLE [dbo].[category]( [categoryId] [uniqueidentifier] NOT NULL [catParentId] [uniqueidentifier] NULL [categoryName] [nvarchar](50) NOT NULL CONSTRAINT [PK_category] PRIMARY KEY CLUSTERED ) catParentId is for main category categoryId is for categories 1 0 Software 2 0 Hardware 3 0 Network 4 1 Microsoft 5 1 IBM 6 2 HP 7 2 DELL etc ...

Pradeep Chandraker · Answer

What is confusing in your question is there is no subcategory table. also you are mapping table itemcategory which provides mapping between item and category table I don't see any mapping or direct column for subcategory. But assuming that you gave there table category subcategory and item with proper relationship here is the answer. What you can do is first you can load only category dropdownlist you can have simple select statement like this in your category stored proc. SELECT categoryId categoryName FROM category ORDER BY categoryName On selection of any category on dropdown you can load corresponding subcategories list you can have simple subcategory stored proc to return list of subcategory based on categoryId something like this: SELECT sub categoryId sub categoryName FROM category WHERE categoryId=@ categoryId ORDER BY sub categoryName Note that you have to pass @ categoryId as a parameter to your subcategory SP. Than finally you can have another stored proc which will return items based on selected subcategory something like this. SELECT itemid  itemname FROM WHERE subcategoryId=@subcategoryId ORDER BY itemname For better performance you can implement AJAX cascaded dropdown. Let me know if you have any question.