joining tables - 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

joining tables

Author

Topic

raindear
Yak Posting Veteran

64 Posts

Posted - 2013-11-18 : 12:47:14

Hi

Fairly new to SQL but wondered if someone could help with a query.

In a table called PromotionLineItem there is a field called DiscountAmount.

Within the same table there is a field called ShoppingCartRecID. This field is also in a table called Orders_ShoppingCart and in Orders_ShoppingCart there is a field called Order number which I would like to join to a table called Orders.

So basically...

Order table contains an Order Number and all order information. I would like to select ALL the information in the Orders table but add the DiscountAmount from the two other tables. Any ideas on how I can go about this?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-18 : 15:58:30

Here's the basic syntax for JOINing tables and using table aliases. You should be able to tweak this to make it work for your actual column names and table names.
Does this help?


select o.[order number]
       ,o.[<other order information>]
       ,pli.DiscountAmount
from   [Order] o
inner join [Orders_ShoppingCart] osc
       on osc.[order Number] = o.[order Number]
inner join PromotionLineItem pli
       on pli.ShoppingCartRecID = osc.ShoppingCartRecID
where  o.[order number] = '<some orderNumber>'

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-19 : 01:32:37

In case it does not have a discount info, you should use left join otherwise it will inore all cases where it doesnt have a discount info

------------------------------------------------------------------------------------------------------
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