February 10, 2010 at 1:14 pm
I have a query to write for a report and my client made a change that has me confused about how to write it.
Background:
I need to show basic info Id, Desc, Qty Instock, Expires all of that info is in the same table Inventory. The client wants to now know when the last time one of the products sold. This is where it gets complicated for me. We keep the date of the sale in the Order table, since we can sell multiple items in an order we have an OrderDetails table which will hold the ProductId. So, I need to link the Inventory ProdId to the OrderDetail ProdId, then link the OrderId of the Detail table to the OrderId of the Order table to get the date.
Prior to me adding the joins, I had 121 records (which is correct), now that I added the joins I am seeing 8129 rows. I pasted my query below, any help would be appreciated. I admit, I get confused on Joins and I am sure that is where my issue is, so if the person who solves my issue can say why it didnt work my way and why it will/should now, I would be thankful. I dont want to get greedy as the solution should be sufficient, but if I know how, I may have to post less and be able to answer others on here.
SELECT I.ProductID, I.Description, I.UnitsInStock, I.UnitPrice, I.Expires, MAX(O.OrderDate) AS LastOrderDate
FROM Order_Details AS OD INNER JOIN
Orders AS O ON OD.OrderID = O.OrderID RIGHT OUTER JOIN
Inventory AS I ON OD.ProductID = I.ProductID
WHERE (I.IsScrip = 1) AND (I.Warehouse = 153079)
GROUP BY I.ProductID, I.Description, I.UnitsInStock, I.UnitPrice, I.Expires, O.OrderDate
Thanks in advance!
February 10, 2010 at 1:29 pm
Please provide table def and some (fake) sample data (enough to show the effect you're faced with) so we have something to play with.
Your join syntax seems to be correct (syntax wise) so it has to do with the data itself.
Please note that we don't see what you see ('cause we can't look over your shoulder...)
Once ewe have sufficient information the solution should be simple...
February 11, 2010 at 2:19 am
As you have an aggregate function on OrderDate it does not make much sense to have it in the GROUP BY clause.
Try:
GROUP BY I.ProductID, I.Description, I.UnitsInStock, I.UnitPrice, I.Expires--, O.OrderDate
Edit: Using a derived table may make the query more efficient (less to group by):
SELECT I.ProductID, I.Description, I.UnitsInStock, I.UnitPrice, I.Expires
,D.LastOrderDate
FROM Inventory I
LEFT JOIN
(
SELECT OD.ProductID
,MAX(O.OrderDate) AS LastOrderDate
FROM Orders O
JOIN Order_Details OD
ON O.OrderID = O.OrderID
GROUP BY OD.ProductID
) D
ON I.ProductID = D.ProductID
WHERE I.IsScrip = 1
AND I.Warehouse = 153079
Also, as you are using SQL2005, the ROW_NUMBER() function may be quicker than GROUP BY:
SELECT I.ProductID, I.Description, I.UnitsInStock, I.UnitPrice, I.Expires
,D.OrderDate AS LastOrderDate
FROM Inventory I
LEFT JOIN
(
SELECT OD.ProductID
,O.OrderDate
,ROW_NUMBER() OVER (PARTITION BY OD.ProductID ORDER BY O.OrderDate DESC) As RowNum
FROM Orders O
JOIN Order_Details OD
ON O.OrderID = O.OrderID
) D
ON I.ProductID = D.ProductID
WHERE I.IsScrip = 1
AND I.Warehouse = 153079
AND D.RowNum = 1
February 11, 2010 at 2:32 am
The reason why you go from 121 to 8129 is called the multiplication effect and is indeed caused by joining the inventory table with the order(details) table. If you have 1 line in inventory for product x but that is sold 5 times, you will now get 5 records instead of one because of the join. The group by and max should take care of that, but not if you put orderdate in the group by -> this will continue creating extra records for each distinct orderdate.
So removing orderdate from the group by should you get already much closer.
You may need to change the inner join to a right outer join too to get correct results. As for style, most people prefer to use left outer joins over right outer joins as they produce more readable queries.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply