Return all records from JOINed tables

[Rattlesnake]

Hi,
I am using SQL Server 2000. I have a database that store the SalesForecast and the SalesORders.
I have the following tables

SalesForecast
-------------
ForecastMonth
ForecastYear
CustomerID
ProductID
ForecastQuantity

SalesOrder
---------
OrderMonth
OrderYear
CustomerID
ProductID
OrderQuantity



I want to create a query that will give me the following information

CustomerID--Month--Year--ProductID--ForecastQuantity--OrderQuantity

i.e a query that will match the Forecast that was for a specific Product and the actual sales ORders that were received for that Product.

What I have done is created a Groupby query (vwTotalOrders) that Sums the Orders for a Product and another Query (vwTotalForecast) that sums the Forecast for that product

vwTotalForecast
---------------
Select CustomerID,ProductID,ForecastMonth,ForecastYear,Sum(ForecastQuantity) as TotalFC
From SalesForecast
GROUPBY CustomerID,ProductID,ForecastMonth,ForecastYear


vwTotalOrders
---------------
Select CustomerID,ProductID,OrderMonth,OrderYear,Sum(OrderQuantity) as TotalOrders
From SalesORder
GROUPBY CustomerID,ProductID,OrderMonth,OrderYear

Now I want to create a join query between these 2 queries that will give me the belwo record
CustomerID--Month--Year--ProductID--ForecastQuantity--OrderQuantity

I have created this query

SELECT vwTotalForecast.CustomerID, vwTotalForecast ,ProductID, vwTotalForecast.ForecastMonth, vwTotalForecast.ForecastYear, vwTotalForecast.TotalFC, vwTotalOrders.TotalOrders, vwTotalOrders.OrderYear, vwTotalOrders.OrderMonth
FROM vwTotalForecast INNER JOIN
vwTotalOrders ON vwTotalForecast.CustomerID = vwTotalOrders.CustomerID AND vwTotalForecast.ForecastYear = vwTotalOrders.OrderYear AND
vwFC.ForecastMonth = vwTotalOrders.OrderMonth AND vwTotalForecast.ProductID = vwTotalOrders.ProductID




But I have one issue with this, there might be some Products that MIGHT NOT be FORECASTED but will have Sales ORders for a specific month. The reverse can be also True i.e. there might be a forecast for a Product but no Orders are placed for that product.

How can I make the query return all the Forecast records even if they donot have a Sales ORder and all Sales ORders even if they donot have Forecast

 

[kejpa]

Hi,
Have you tried outer join?

/Kejpa

 

[Joe Mamma]

depending on performance, either of these should work -

Select sf.CustomerID, sf. ProductID, sf.ForecastMonth TheMonth, sf.ForecastYear TheYear ,Sum(sf.ForecastQuantity) as TotalFC, Sum(sf.OrderQuantity) TotalOrders
From SalesForecast sf left join SalesOrder so on 
sf.CustomerID = so.CustomerID and 
sf.ProductID = sf.ProductID and 
sf.ForecastMonth = so.OrderMonth and 
sf.ForecastYear = so.OrderYear 
GROUPBY sf.CustomerID,sf.ProductID, so.ForecastMonth,so.ForecastYear
union
Select so.CustomerID, so. ProductID, so.OrderMonth , so.OrderYear , sum(0) , Sum(OrderQuantity) TotalOrders
From SalesOrder so left join SalesForecast sf 
so.CustomerID = so.CustomerID and 
so.ProductID = sf.ProductID and 
so.OrderMonth = sf.ForecastMonth and 
so.OrderYear = sf.ForecastYear where 
sf.CustomerId is null
GROUPBY so.CustomerID,sof.ProductID, so.OrderMonth, so.OrderYear

or

select CustomerID, ProductID, TheMonth, TheYear, sum(ForecastQuantity) TotalForcast, sum(OrderQuantity) TotalOrder 
from
(
Select CustomerID, ProductID, ForecastMonth TheMonth, ForecastYear TheYear, ForecastQuantity, 0 OrderQuantity from SalesForecast 
union
Select CustomerID, ProductID, OrderMonth , OrderYear, 0 ForecastQuantity, OrderQuantity from SalesOrder
) temp group by CustomerID, ProductID, TheMonth, TheYear