I have a query that I am having problems with.
The problem is that table d (quantity_map) can have two rows with the same qbpos_id, thus when it does the conditional IF statement it will add the quantity twice if there are two rows and three times if there are three rows.
The data is correct and it is possible for there to be multiple rows so I need to account for that but I cannot think of a good way to handle this, I thought about doing sub query to remove the duplicates and get it to be distinct but I figured there has to be an easier way.
For example purposes of what I mean by this.
ProdID 101 has qbpos_id of 5000
ProdID 455 has qbpos_id of 5000
The ProdID does not matter for determining the Quantity on Hand, only the qbpos_id matters since the underlying product is identical. However, when it does the SUM it will sum ProdID 101 and ProdID 455 together effectively saying that twice as much inventory of qbpos_id 5000 exists, when it doesn't.
This is complicated and I am sure someone will tell me that it is a flaw in the data model but ProdID is our online product ID and qbpos_id is what the online product is mapped two and for SEO and Search purposes we double list the product with different names and map to the back-end product.
So, for those of you that understand the problem do you have any idea on how I can write the below query to select the information for DISTINCT qbpos_id's? Can I change it into a JOIN and do some sort of DISTINCT clause in a JOIN???? I couldn't find anything about this.
I am running MySQL 5.
Thanks in advance, I am assigning 500 points but wish I could do much more....this one has me stumped and I am sure is confusing enough to warrant more.
SELECT *, e.ProdID, b.Descr, b.Size, b.Attribute, b.ItemNumber, auxMinimumQty, SUM(IF(StoreID='6',Quantit
yOnHand,0)
) AS AUX, SUM(IF(StoreID='1',Quantit
yOnHand,0)
) AS BRK, IF(auxMinimumQty-(SUM(IF(S
toreID='1'
,QuantityO
nHand,0)))
>=SUM(IF(S
toreID='6'
,QuantityO
nHand,0)),
'N','Y') as ENOUGH, c.ListID
FROM item_inventory b, item_store_inventory c, quantity_map d, products e
WHERE d.qbpos_id=b.ItemNumber
AND b.ListID=c.ListID
AND d.prodid=e.prodid
AND e.deleted='N'
AND StoreID IN (1,6)
AND e.prodid
GROUP BY b.ItemNumber
HAVING AUX<auxMinimumQty AND BRK!=0 AND auxMinimumQty>0
ORDER BY ENOUGH desc, BayNumber, ShelfNumber, ShelfPosition
