Mapping remaining coupons - 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

Mapping remaining coupons

Author

Topic

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-10-10 : 12:02:42

Hi

I have two SQL tables

Coupon and Coupon CouponStore

In Coupon there is a column of CouponID and in CouponStore the same column name.

Also in CouponStore there is a column called StoreID that has the value of each store 1, 2, 3 and 4.

How would I write a script to map any CouponID in the Coupon table to each Store Value that are not already mapped to a StoreID?

Thank you

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-10 : 12:27:37

This - assuming you have only the four stores:

SELECT
	a.CouponId,
	b.StoreId
FROM
	Coupon a
	CROSS JOIN (VALUES (1), (2), (3),(4)) AS b(StoreId)
WHERE NOT EXISTS
(
	SELECT * 
	FROM CouponStore c 
	WHERE c.CouponId = a.CouponId AND c.StoreId = b.StoreId
);

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-10-10 : 12:41:32

Did not work but thanks for trying. Will play around with the code to see if I can get it working

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-10 : 12:44:02

Did it give the wrong results, or a parsing error, or no results at all?

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-10-10 : 12:46:29

It did execute but the coupons are still not mapped to the stores.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-10 : 12:55:06

By mapping, if you meant that you want to insert the rows of that query into the CouponStore table, add an insert clause at the beginning.

INSERT INTO CouponStore (CouponId, StoreId)
SELECT
	a.CouponId,
	b.StoreId
FROM
	Coupon a
	CROSS JOIN (VALUES (1), (2), (3),(4)) AS b(StoreId)
WHERE NOT EXISTS
(
	SELECT * 
	FROM CouponStore c 
	WHERE c.CouponId = a.CouponId AND c.StoreId = b.StoreId
);

That will add rows to your table, so run that only if you are sure that that is what you want to do, and that the results of the select query are correct.

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-10-10 : 13:10:10

Perfect! Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources