How to show all records from multiple tables regardless of match on join statement

Question

I am trouble figuring out the proper syntax to structure this query correctly. I am trying to show ALL records from both the SalesHistoryDetail AND from the SalesVsBudget table. I believe my query allows for some of the records on SalesVsBudget to not be pulled, whereas I want them all for that period, regardless of whether there was a corresponding sale. Here is my code:

SELECT  MAX(a.DispatchCenterOrderKey) AS DispatchCenter, 
        a.CustomerKey, 
CASE WHEN a.CustomerKey IN
             (SELECT AddressKey
              FROM FinancialData.dbo.DimAddress
              WHERE AddressKey >= 99000 AND AddressKey <= 99599) THEN 1 ELSE 0 END AS InterCompanyFlag, 
    MAX(a.Customer) AS Customer, 
        a.SalesmanID, 
    MAX(a.Salesman) AS Salesman, 
        a.SubCategoryKey, 
    MAX(a.SubCategoryDesc) AS Subcategory, 
    SUM(a.Value) AS SalesAmt, 
        b.FiscalYear AS Year, 
        b.FiscalWeekOfYear AS Week, 
    MAX(c.BudgetLbs) AS BudgetLbs, 
    MAX(c.BudgetDollars) AS BudgetDollars
FROM   dbo.SalesHistoryDetail AS a 
LEFT OUTER JOIN dbo.M_DateDim AS b ON a.InvoiceDate = b.Date 
FULL OUTER JOIN dbo.SalesVsBudget AS c ON a.SalesmanID = c.SalesRepKey 
                                  AND a.CustomerKey = c.CustomerKey 
                                  AND a.SubCategoryKey = c.SubCategoryKey 
                                  AND b.FiscalYear = c.Year AND b.FiscalWeekOfYear = c.WeekNo
GROUP BY a.SalesmanID, a.CustomerKey, a.SubCategoryKey, b.FiscalYear, b.FiscalWeekOfYear

There are two different data sets that I am pulling from, obviously the SalesHistoryDetail table and the SalesVsBudget table. I'm hoping to get ALL budgetLbs, and BudgetDollars values from the SalesVsBudget table regardless of whether they match in the join. I want all of the matching joining records too, but I also want EVERY record from SalesVsBudget. Essentially I want to show ALL sales records and I want to reference the budget values from SalesVsBudget when the salesman,customer,subcategory, year and week match but I also want to see budget entries that fall in my date range that don't have corresponding sales records in that period. Hopefully that makes sense. I feel I am very close, but my budget numbers doesn't reflect the whole story and I think that is because some of my records are being excluded! Please help.

score 0 · Accepted Answer · answered Mar 15 '17 at 16:49

I was able to accomplish this through playing with the FULL OUTER JOIN. My problems was there were more records in SalesVsBudget than SalesHistory_V. Therefore I had to make SalesVsBudget the initial FROM table and SaleHistory_V with a FULL OUTER JOIN and all records lined up.

How to show all records from multiple tables regardless of match on join statement

1 Answers1