help joining tables

[spiff2002]

This could be a simple query. I'm just stuck on it.

I have two tables (this is no t the best design but I'm wondering if I can't get the result that I want)

sales_table (this holds sales YTD)

brand division region cases_sold
-----------------------------------------------
brand1 south region1 200
brand1 north region3 300
brand2 north region2 400

The second table
orders_table (this holds open orders)

brand division region cases_ordered
-----------------------------------------------
brand1 south region1 200
brand1 north region3 300
brand2 north region2 400
brand3 south region1 100

when I run the query
the result should be something like

brand division region ordered+sold
-----------------------------------------------
brand1 south region1 400
brand1 north region3 600
brand2 north region2 800
brand3 south region1 100


region1 only belongs to south division
regions 2 and 3 only belong to north region

does any one have any ideas??


Thanks in advance

S.

 

[twifosp]

Search for JOIN in BOL.

select	s.brand,
	s.division,
	s.region,
	'total' = (s.cases_sold + o.cases_ordered)
from	sales_table s
left	outer join orders_table o
on	o.brand = s.brand
and	o.division = s.division
and	o.region = s.region

 

[spiff2002]

That query doesn't bring the last line

brand3 south region1 100