merging two resuls - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

merging two resuls

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-05-15 : 00:02:27

Hi all

I have a shopping cart where user can add items to cart with and without login. when a user logs in and add items to cart his user id and a unique id( a guid) is saved in db. if user isnt login then guid and userid = 0 is saved in db. my problem is that if a user logs in and add two item to cart like following

productid guid userid qty

1 1sdf 1 1

2 1sdf 1 2

and signs out. then he add 2 more item to cart

productid guid userid qty

2 1xxx 0 1

3 1xxx 0 1

I want to merg these two records such that if item was already in cart then quantity must be incremented by 1 else data sets should be merged. so result should b like this:

productid guid userid qty

1 1sdf 1 1

2 1sdf 1 3

3 1xxx 1 1

what will be sql for this ? any link or TSQL code or hint please.

Regards,

Asif Hameed

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-15 : 00:25:18

How do you know the additional 2 items with userid = 0 belongs to userid 1 and not other user ?

KH
[spoiler]Time is always against us[/spoiler]

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-05-15 : 01:32:56

when user presses login button, I have userid of user. So I match that if this user has already products with same iD's as I am inserting.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-15 : 01:42:55

i mean if there are more than 2 users performing the same action, like
userid 1 added 2 items then signout and then added 2 more items
userid 2 added 2 items then signout and then added 2 more items
at this instance, you have 4 lines with userid 0. How do you differentiate that.

KH
[spoiler]Time is always against us[/spoiler]

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-05-15 : 05:24:55

there are two fields that make a record unique. userid or cartid. cart id will be different for these two users.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-15 : 05:48:54

[code]
declare @data table
(
productid int,
guid varchar(10),
userid int,
qty int
)

insert into @data
select 1, '1sdf', 1, 1 union all
select 2, '1sdf', 1, 2 union all
select 2, '1xxx', 0, 1 union all
select 3, '1xxx', 0, 1

declare @userid int

select @userid = max(userid)
from @data
where userid <> 0

select d.productid,
guid = coalesce(max(case when d.userid = 0 then null else d.guid end), max(d.guid)),
userid = @userid,
qty = sum(d.qty)
from @data d
group by d.productid
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-05-15 : 20:15:05

following procedure works exactly what i want except that it adds 1 to quantity. for example, if there is an item which contains 2 as quantity, then user adds another item with quantity 3, instead of returning 5 as quantity for that item, it returns 3. I have pointed out probelm where I need to change code to increment the quantity properly.

ALTER Procedure [dbo].[AttachShoppingCartToUser]
(
@CartID char(36),
@UserID int,
@ProductID Varchar
)
AS
IF EXISTS
(SELECT CartID
FROM ShoppingCart
WHERE ProductID in (select value from fn_SplitToList(@ProductID)) AND CreatedBy = @UserID)
begin
UPDATE ShoppingCart

' change this line
SET Quantity = Quantity + 1
WHERE ProductID in (select value from fn_SplitToList(@ProductID)) AND CreatedBy = @UserID

UPDATE ShoppingCart
set CreatedBy = @UserID
where CartID = @CartID and CreatedBy = 0 and ProductID not in (select value from fn_SplitToList(@ProductID))

delete from ShoppingCart
WHERE CartID = @CartID
and ProductID in (select value from fn_SplitToList(@ProductID))
and CreatedBy = 0

end

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-05-15 : 20:39:26

any suggestion please. I need it urgently

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-05-19 : 03:57:40

any one to suggest me solution to this ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources