Check for applicable Group query for shopping cart

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

SQL Server 2008 Forums

Transact-SQL (2008)

Check for applicable Group query for shopping cart

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2012-08-03 : 02:46:46

Hi,

I have problem for one of discount check condition. I have tables structure as below:
Cart table (id, customerid, productid)
Group table (groupid, groupname, discountamount)
Group Products table (groupproductid, groupid, productid)

While placing an order, there will be multiple items in cart, I want to check those items with top most group if that group consists of all product shopping cart have?

Example: If group 1 consists 2 products and those two products exists in cart table then group 1 discount should be returned.

please help

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-03 : 07:13:23

Would this give you the results you are expecting?

SELECT
	c.customerId,
	gp.groupId,
	CASE WHEN COUNT (DISTINCT c.productId) =  COUNT (gp.productId) THEN DiscountAmount ELSE 0 END
		AS DiscountApplied
FROM
	CartTable c
	INNER JOIN GroupProductsTable gp
		ON gp.productId = c.productId
GROUP BY
	c.customerId,
	gp.groupId;

keyursoni85
Posting Yak Master

233 Posts

Posted - 2012-08-06 : 05:13:11

Hi,

Thanks for your reply.
No, there is one other table which is group master under which multiple products are allocated to that group.

Now I added few products in shopping cart and from created group.. if any group matches to my shopping cart products then that group will be applicable.

For example..
shopping cart holds two products with Product ID 1 and 2
"GROUP 1" holds two products with same ID then that group will be applicable.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-06 : 06:52:46

I should have included the Group Table in the join.

SELECT
	c.customerId,
	gp.groupId,
	CASE WHEN COUNT (DISTINCT c.productId) =  COUNT (gp.productId) THEN g.DiscountAmount ELSE 0 END
		AS DiscountApplied
FROM
	CartTable c
	INNER JOIN GroupProductsTable gp
		ON gp.productId = c.productId
	INNER JOIN GroupTable g ON
		g.groupid = gp.groupid
GROUP BY
	c.customerId,
	gp.groupId;

keyursoni85
Posting Yak Master

233 Posts

Posted - 2012-08-07 : 10:41:44

Hi, Thanks for your reply but it is not giving proper results. I given table structure and sample data below and output required too.


CREATE TABLE [dbo].[carttable](
	[cartid] [int] IDENTITY(1,1) NOT NULL,
	[customerid] [int] NULL,
	[productid] [int] NULL,
 CONSTRAINT [PK_carttable] PRIMARY KEY CLUSTERED 
(
	[cartid] 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].[groups](
	[groupid] [int] IDENTITY(1,1) NOT NULL,
	[groupname] [varchar](50) NULL,
	[groupamount] [money] NULL,
 CONSTRAINT [PK_groups] PRIMARY KEY CLUSTERED 
(
	[groupid] 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].[groupproducts](
	[groupproductid] [int] IDENTITY(1,1) NOT NULL,
	[groupid] [int] NULL,
	[productid] [int] NULL,
 CONSTRAINT [PK_groupproducts] PRIMARY KEY CLUSTERED 
(
	[groupproductid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
---------------------
-- INSERT SCRIPT

SET IDENTITY_INSERT [dbo].[carttable] ON;

BEGIN TRANSACTION;
INSERT INTO [dbo].[carttable]([cartid], [customerid], [productid])
SELECT 1, 1, 1 UNION ALL
SELECT 2, 1, 2 UNION ALL
SELECT 3, 1, 3
COMMIT;
GO

SET IDENTITY_INSERT [dbo].[carttable] OFF;
-------------------
SET IDENTITY_INSERT [dbo].[groups] ON;

BEGIN TRANSACTION;
INSERT INTO [dbo].[groups]([groupid], [groupname], [groupamount])
SELECT 1, N'grp1', 152.0000 UNION ALL
SELECT 2, N'grp2', 554.0000 UNION ALL
SELECT 3, N'grp3', 33.0000
COMMIT;
RAISERROR (N'[dbo].[groups]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO

SET IDENTITY_INSERT [dbo].[groups] OFF;
---------------------

SET IDENTITY_INSERT [dbo].[groupproducts] ON;

BEGIN TRANSACTION;
INSERT INTO [dbo].[groupproducts]([groupproductid], [groupid], [productid])
SELECT 1, 1, 1 UNION ALL
SELECT 2, 1, 2 UNION ALL
SELECT 3, 2, 3 UNION ALL
SELECT 4, 2, 2 UNION ALL
SELECT 5, 3, 1 UNION ALL
SELECT 6, 3, 4
COMMIT;
GO

SET IDENTITY_INSERT [dbo].[groupproducts] OFF;
-----------------

Above data have 3 groups and shopping cart table contains 3 record applicable to group 1..
group 1 consists 2 products which matches shopping cart products than other group products.
* so group 1 only should return with its amount.

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources