Amending query to get info from new table

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

Amending query to get info from new table

Author

Topic

raindear
Yak Posting Veteran

64 Posts

Posted - 2013-10-19 : 17:48:32

Hi

I used to use this code to extract coupon usage from two tables orders and coupons

select distinct o.OrderDate, p.SKU, c.CustomerID, c.CustomerLevelID, c.Email, c.FirstName, c.LastName from customer c
join dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerID
join dbo.Product p with (NOLOCK) on p.ProductID = os.ProductID
join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber
and c.IsRegistered = 1
and c.CustomerLevelID = '4'
and p.SKU like '%'
and o.CouponCode = "ta25"
order by o.OrderDate, p.SKU, c.CustomerID

Which executed fine.

Following a database upgrade the Coupon Code is no longer used. Instead there are two new SQL tables called Promotions and PromotionsUsage

The link between the two tables are

ID in Promotions

and

PromotionID in PromotionUsage

Both are the same value. So in order to see new orders for coupon usage how would I change this line in the original SQL to retireve this? It's driving me mad!

and o.CouponCode = "ta25"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-20 : 03:23:35

Your question doesnt specify which column(s) in Promotion and PromotionUsage has details on coupon to replace the current filter.
Also how are tables related to Product or any other tables currently used in query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

raindear
Yak Posting Veteran

64 Posts

Posted - 2013-10-20 : 04:00:22

Ok so the tables are

Orders
Promotions
PromotionUsage

Within the Order table there is column OrderNumber and within the PromotionUsage there is OrderID these are the same value just named differently.

So these are the two that would replace the o.CouponCode in the script but how can I link these tables? Each time I try to use a JOIN command it comes back empty

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-20 : 04:05:38

somehing like below


select distinct o.OrderDate, p.SKU, c.CustomerID, c.CustomerLevelID, c.Email, c.FirstName, c.LastName 
from customer c
join dbo.Orders_ShoppingCart os with (NOLOCK) on os.CustomerID = c.CustomerID
join dbo.Product p with (NOLOCK) on p.ProductID = os.ProductID
join dbo.Orders o with (NOLOCK) on o.OrderNumber = os.OrderNumber
join dbo.PromotionUsage pu on pu.OrderID = o.OrderNumber 
join dbo.PromotionID pm ON pm.ID = pu.PromotionID 
and c.IsRegistered = 1
and c.CustomerLevelID = '4'
and p.SKU like '%'
and <your promotion filter condition here>
order by o.OrderDate, p.SKU, c.CustomerID

Also I hope you're aware of issues with using NOLOCK hint

Make sure you read this

http://visakhm.blogspot.in/2010/02/avoiding-deadlocks-using-new.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

raindear
Yak Posting Veteran

64 Posts

Posted - 2013-10-20 : 04:37:56

Thanks! Tweaked it a little and works fine :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-20 : 06:13:56

cool
Have a look at article as well and then decide whether you need to use NOLOCK

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources