July 3, 2015 at 8:59 am
Hi,
I have 3 tables as per following:
orddet
OrderProductQtyOrd
1 Item1 20
2 Item1 10
3 Item2 10
4 Item1 5
4 Item2 5
ordhead
OrderDate
110/06/2015
205/07/2015
307/06/2015
415/08/2015
product
ProductdescMinQty
Item1This is 110
Item2This is 220
I want to pull only the 1 line for minqty for an item as follows
OrderProductQtyOrddate minqty
1 Item1 20 10/06/2015 10
2 Item1 10 05/07/2015
3 Item2 10 07/06/2015 20
4 Item1 5 15/08/2015
4 Item2 5 15/08/2015
Can anyone help?
regards
james
July 3, 2015 at 10:18 am
SELECT o.[Order], o.Product, o.qtyOrd, oh.[Date], P.MinQty
FROM ORDDET O
INNER JOIN ORDHEAD OH ON OH.[Order] = O.[Order]
LEFT JOIN (SELECT o.Product, MIN(o.[Order]) MinOrder
FROM ORDDET O
GROUP BY o.Product
) mino ON mino.MinOrder = o.[Order]
LEFT JOIN PRODUCT P ON P.Product = o.Product AND p.Product = mino.Product
There's probably more performance-friendly ways to do it, but this should get you started.
July 6, 2015 at 1:53 am
Thank you Grasshopper.. Perfect!
James
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply