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]
Loading
Trifon DinevPosted Jun 16, 2011, 12:24 PM
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 ChandrakerPosted Jun 16, 2011, 10:20 AM
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 subcategoryId, subcategoryName FROM category WHERE categoryId=@categoryId ORDER BY subcategoryNameNote 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.