Cross join behavior

Question

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.

I found this SO question and mimicked Tony Andrews' answer to a point but it isn't acting as I would expect.

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:

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]

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.

The SQL I am using with the date range is:

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] >= '1/1/2013' AND u.[Date] <= '1/31/2013')
GROUP BY i.WholesaleCost, i.RetailPrice, i.OnHandQuan, u.ItemNo, l.[Description], l.LocationID, i.[Description]
ORDER BY l.[Description]

I found this article on MSDN that talks about Cross joins acting like inner joins when they include a where clause but one of the comments states that that only applies when the where dictates the join criteria. Since the where of my SQL is just limiting the date range I'm assuming that's not the issue.

Any direction on this would be very appreciated.

fourpastmidnight · Accepted Answer · 2013-07-12T22:47:07.450

You probably don't need a CROSS JOIN. They aren't used all that much in practice (rarely, they're very useful, but rarely).

Anyway, you may want to do some research into [LEFT|RIGHT] [OUTER] JOINs (the OUTER keyword is optional). With INNER JOINs, records must exist in each table that is joined. With OUTER JOINs, a record must exist in at least one table in order for data to be returned. Which table is determined by whether you're performing a LEFT JOIN or a RIGHT JOIN.

I've taken a stab at writing the correct query below--but I don't know your data model and made some "intuitive guesses". Play with that and see what you can come up with.

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 
    INNER JOIN RetailSaleItems as i ON i.LocationID = l.LocationID
    LEFT JOIN RetailSaleUsage as u ON l.LocationID = u.LocationID
WHERE (l.LocationID IN(1)) AND (i.Inactive = 0) AND (i.ItemNo = u.ItemNo OR u.ItemNo IS NULL)
GROUP BY i.WholesaleCost, i.RetailPrice, i.OnHandQuan, u.ItemNo, l.[Description], l.LocationID, i.[Description]
ORDER BY l.[Description]

UPDATE: To help you answer your question per your comment below, you need to change where you're restricting by date range. Currently, you're restricting all results by the specified date range in the RetailSaleUsage table because the condition is placed in the WHERE clause. Instead, we need to specify the restriction as a condition of JOIN on the join to the RetailSaleUsage table. Have a look at the query below. That should do it for you.

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 
    INNER JOIN RetailSaleItems as i ON i.LocationID = l.LocationID
    LEFT JOIN RetailSaleUsage as u ON l.LocationID = u.LocationID AND u.[Date] BETWEEN '1/1/2013' AND '1/31/2013'
WHERE (l.LocationID IN(1)) AND (i.Inactive = 0) AND (i.ItemNo = u.ItemNo OR u.ItemNo IS NULL)
GROUP BY i.WholesaleCost, i.RetailPrice, i.OnHandQuan, u.ItemNo, l.[Description], l.LocationID, i.[Description]
ORDER BY l.[Description]

HTH.

I tried something like this originally but it is only returning items that have activity within the date range not all items. — Mike_OBrien, Jul 12 '13 at 19:47
Ah, I see what you're saying. Looking at your query, you're restricting by date range in the `WHERE` clause. That restricts the results of the entire query. Instead, you'll want to restrict by date range in your join to `RetailSaleUsage` as an additional `JOIN` condition. I'll modify my answer above to show you. — fourpastmidnight, Jul 12 '13 at 22:42

Cross join behavior

1 Answers1