I am trying to select three random rows from a table, where their combined item_price column is less than a desired amount.
Imagine you have an for a dollar amount. When you enter the dollar amount, the database returns three random items, where their combined price is less than or equal to the dollar amount you enter.
If I enter $300, you could buy these three items, $150, $100, and $50. I'm having difficulty creating a query that will return three items that meet this criteria.
SELECT t1.item_id, t1.item_price
FROM items t1
INNER JOIN items t2 ON ( t1.item_id = t2.item_id )
GROUP BY t1.item_id, t1.item_name, t1.item_price
HAVING SUM( t2.item_price ) <=300
ORDER BY RAND( )
LIMIT 3
I thought this would work, but I think it was just a coincidence when it did. It seems to just return any three items whose prices are less than $300, not total less than $300.
I also tried this query:
SELECT t1.item_id, t1.item_price
FROM items t1
JOIN items t2 ON t2.item_id <= t1.item_id
WHERE t2.item_price <= 500
GROUP BY t1.item_id
HAVING SUM(t2.item_price) <= 500
ORDER BY RAND()
LIMIT 3
Again, seemed to work at first, but then it started returning items for $2000.
If there's a better (even sacrificing performance) way to do this in PHP, I don't mind. I just didn't think the query would be so difficult.
As always, thanks anyone for the help.
解决方案
here is another solution:
SELECT t1.item_id as id1, t2.item_id as id2, t3.item_id as i3
FROM items t1, items t2, items t3
WHERE
t1.item_id <> t2.item_id and
t1.item_id <> t3.item_id and
t2.item_id <> t3.item_id and
(t1.item_price + t2.item_price + t3.item_price) <= 300
order by rand()
limit 1
optionally you can filter by minimal sum