dcclassics dcclassics - 2 years ago 131
MySQL Question

MySQL Select 3 random rows where sum of three rows is less than value

I am trying to select three random rows from a table, where their combined

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

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

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.

Answer Source

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
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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download