Cesar Cesar - 1 year ago 62
SQL Question

Join is not showing the right amount of rows

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'

Answer Source

If 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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download