title: Cross join behavior
body: <p>I have a database that keeps track of retail sales based on Location. I have a location table with an ID and various other location specific information, a retail sale item table with an itemID and all other item information, and a retail sale usage table that has a combination of location and item ID's as well as other information about the sale(quantity, sale price, etc). I am trying to make a query that will return all retail sale items and the sum of the quantity sold for each for a given date range grouped by location.</p>
  
  <p>I found <a href="https://stackoverflow.com/questions/523091/sql-cross-join-what-use-has-anyone-found-for-it">this</a> SO question and mimicked Tony Andrews' answer to a point but it isn't acting as I would expect.</p>
  
  <p>I can get a complete list of items(all 29 items are retuned as expected) and quantities sold if I do not include the date range:</p>
  
  <pre><code>SELECT i.WholesaleCost, i.RetailPrice, i.OnHandQuan, u.ItemNo, AVG(u.Price) AS Price, SUM(u.Quantity) AS Quantity, l.Description, l.LocationID, i.Description AS ItemDescription 
  FROM Location as l CROSS JOIN RetailSaleItems as i
    INNER JOIN RetailSaleUsage as u ON l.LocationID = i.LocationID AND i.ItemNo = u.ItemNo
  WHERE (l.LocationID IN(1)) AND (i.Inactive = 0)
  GROUP BY i.WholesaleCost, i.RetailPrice, i.OnHandQuan, u.ItemNo, l.[Description], l.LocationID, i.[Description]
  ORDER BY l.[Description]
  </code></pre>
  
  <p>As soon as I try to limit the date range, say to January of this year, I lose 5 records and as best I can tell its because they don't show in the RetailSaleusage table within the date range specified. But that doesn't make sense because there are 15 other items that don't show up in that date range either but they appear in the results.</p>
  
  <p>The SQL I am using with the date range is:</p>
  
  <pre><code>SELECT i.WholesaleCost, i.RetailPrice, i.OnHandQuan, u.ItemNo, AVG(u.Price) AS Price, SUM(u.Quantity) AS Quantity, l.Description, l.LocationID, i.Description AS ItemDescription 
  FROM Location as l CROSS JOIN RetailSaleItems as i
    INNER JOIN RetailSaleUsage as u ON l.LocationID = i.LocationID AND i.ItemNo = u.ItemNo
  WHERE (l.LocationID IN(1)) 
    AND (i.Inactive = 0) AND (u.[Date] &gt;= '1/1/2013' AND u.[Date] &lt;= '1/31/2013')
  GROUP BY i.WholesaleCost, i.RetailPrice, i.OnHandQuan, u.ItemNo, l.[Description], l.LocationID, i.[Description]
  ORDER BY l.[Description]
  </code></pre>
  
  <p>I found <a href="http://msdn.microsoft.com/en-us/library/ms190690%28v=sql.105%29.aspx" rel="nofollow noreferrer">this</a> article on MSDN that talks about Cross joins acting like inner joins when they include a <code>where</code> clause but one of the comments states that that only applies when the <code>where</code> dictates the join criteria. Since the <code>where</code> of my SQL is just limiting the date range I'm assuming that's not the issue.</p>
  
  <p>Any direction on this would be very appreciated.</p>