I have no idea why this is not working, my first table dbo.tsoSalesAnalysis has 699 rows for the month of July, and all I am trying to do is ADD an addition column from the dbo.[slSalesOrderTable] with a simple join using the custKey as reference. However, it gives me all of the rows for the other table which is over 400,000 rows. IT SHOULD ONLY RETURN 699 ROWS + A NEW COLUMN (CustID)
FROM [dbo].[tsoSalesAnalysis] as t
inner join [dbo].[slSalesOrderTable] as s
on t.CustKey = s.CustKey
WHERE t.PostDate >= '2016-07-01' and t.CustKey= '58888'
CustKey is not unique in
tsoSalesOrdersTable, then we'd expect more than 699 rows to be returned.
Ideally, you would have a different table, which has
CustKey as a UNIQUE column, to lookup the value of
CustId. Absent that, you can get a single value out of the
tsoSalesOrdersTable using an inline view. For example:
SELECT t.ItemKey , S.CustID , t.PostDate , t.ReturnAmt , t.ReturnsQty , t.SalesAmt , t.SalesQty , t.TranDate FROM [dbo].[tsoSalesAnalysis] t JOIN ( -- inline view to return one row for CustKey SELECT ms.CustKey , MIN(ms.CustId) AS CustId FROM [dbo].[slSalesOrderTable] ms WHERE ms.CustKey= '58888' GROUP BY ms.CustKey ) s ON s.CustKey = t.CustKey WHERE t.PostDate >= '2016-07-01'
If the specified value of CustKey 58888 does not appear in slSalesOrderTable, then the inline view will return zero rows. And that means the query will return zero rows, due to the inner join.
If you choose to use an outer join, then you would want to include a predicate on
t.CustKey on the outer query as well.