March 6, 2006 at 4:52 pm
Hi,
Can any one help with this update query. There are four tables shoppingcartproducts, shoppingcartoptions, productoptions, productoptiondetails.
All the products added by the users are stored in the shoppingcartproducts table. Each product can have additional options, like size, color etc. These options selected by the users are stored in the shoppingcartoptions table.
Productoptions table has all the available options and productoptiondetails has all the allowed values for each option.
-------------------------------------------------
productoptions
(optionid, productid, optionname)
200, 1027, Size
201, 1028, Waist
202, 1028, Color
-------------------------------------------------
productoptiondetails
(optiondetailid, optionid, optiondetailname)
124, 200, Small
125, 200, Medium
126, 200, Large
127, 201, 30 Inches
128, 201, 32 Inches
129, 202, Black
130, 202, Red
131, 202, White
-------------------------------------------------
shoppingcartproducts
(productid, productname)
1027, Shirt
1028, Pant
-------------------------------------------------
shoppingcartoptions
(productid, optionid, optiondetailid)
1027, 200, 124
1028, 201, 128
1028, 202, 131
-------------------------------------------------
I want to update the productname in shoppingcartproducts table to include all the options selected by the user, stored in the shoppingcartoptions table. Like this
shoppingcartproducts
(productid, productname)
1027, Shirt Size Small
1028, Pant Waist 32 Inches Color White
Any help.
Thanks.
March 7, 2006 at 2:27 am
There is a well-known trick for this, which uses a function. Please see code below.
-- Table definitions
create table productoptions
(optionid int, productid int, optionname varchar(10))
insert productoptions select 200, 1027, 'Size'
insert productoptions select 201, 1028, 'Waist'
insert productoptions select 202, 1028, 'Color'
create table productoptiondetails
(optiondetailid int, optionid int, optiondetailname varchar(10))
insert productoptiondetails select 124, 200, 'Small'
insert productoptiondetails select 125, 200, 'Medium'
insert productoptiondetails select 126, 200, 'Large'
insert productoptiondetails select 127, 201, '30 Inches'
insert productoptiondetails select 128, 201, '32 Inches'
insert productoptiondetails select 129, 202, 'Black'
insert productoptiondetails select 130, 202, 'Red'
insert productoptiondetails select 131, 202, 'White'
create table shoppingcartproducts
(productid int, productname varchar(100))
insert shoppingcartproducts select 1027, 'Shirt'
insert shoppingcartproducts select 1028, 'Pant'
create table shoppingcartoptions
(productid int, optionid int, optiondetailid int)
insert shoppingcartoptions select 1027, 200, 124
insert shoppingcartoptions select 1028, 201, 128
insert shoppingcartoptions select 1028, 202, 131
go
-- Function definition
create function shoppingcartstring(@productid int)
returns varchar(8000)
as
begin
declare @options varchar(8000)
select @options = case when @options is null then '' else @options + ' ' end + po.optionname + ' ' + pd.optiondetailname
from shoppingcartoptions so
inner join productoptions po on po.productid = so.productid and po.optionid = so.optionid
inner join productoptiondetails pd on pd.optionid = po.optionid and pd.optiondetailid = so.optiondetailid
where so.productid = @productid
order by po.optionid
return @options
end
go
-- Update statement
update shoppingcartproducts set productname = productname + ' ' + dbo.shoppingcartstring(productid)
March 7, 2006 at 9:15 am
Thanks much.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply