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