I have a problem I am sure is simple to solve but it has proved to be beyond
me.
Imagine I have the following data...
StockID | Data1 | Data2
-----------------------
1 | 1 | 2
2 | 3 | 4
3 | 5 | 6
And I want a query to return....
StockID | Data1 | Data2
-----------------------
3 | 5 | 6
I am trying to use
SELECT max(stockid) as stockid, data1, data2
FROM table
HAVING data1 = 5
Unfortunately this is returning the following data....
StockID | Data1 | Data2
-----------------------
3 | 5 | 2
Ie: the correct stockid and data1 but data2 from a diferent row!
I have tried using a sub-query but it just crashes the server (trying to do
"WHERE StockID IN (over 7000 results from sub-query)".
I am using MySQL and the actual query in question is below for your ref...
SELECT s.ItemID, s.ASIN, MAX(q.StockID) AS StockID, q.Condition,
q.MaximumSalePrice, q.MinimumSalePrice, q.AddedToFile, q.CurrentAmazonPrice
FROM stockitemdetails s
JOIN stockquantities q ON (s.ItemID = q.ItemID)
WHERE q.Quantity > 0
AND s.ASIN != ""
AND s.ASIN IS NOT NULL
AND s.ASIN != "N/A"
AND LEFT(s.ASIN,1) != "<"
GROUP BY s.ASIN
HAVING AddedToFile = 0
ORDER BY s.ItemID
--
Jay
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211527
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
