How to find ids with sum of multiple rows in mysql?

Question

Here is my mysql table.

CREATE TABLE IF NOT EXISTS  tbl_money  (
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   amount  int(11) NOT NULL,
   used  int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

INSERT INTO  tbl_money  ( id ,  amount ,  used ) VALUES
(8, 2, 0),
(9, 4, 0),
(10, 4, 0),
(11, 3, 0),
(12, 8, 0),
(13, 10, 0),
(14, 13, 0);

Id is a primary key field and money can have any number from 1 to any number.

Problem: Suppose i have to find id from the table where amount is 8. It is simple because id 5 contains amount 8. But if i have to find id with 14 amount which does not exist. I have to select 2 or more fields now which sum will be equal to 14. For example in above table i can select id 6 and id 3 which amount sum is equal to 14. Same as if i need to find 6 i have to select id 1 and 2 because their amount sum is equal to six.

Sometime there we may need to select more than 2 rows to make equal sum. But if any condition does't match with all rows of the table, we can return 0.

Condition: The row which is already taken that cannot be selected again. Used field should always be 0. If used fields value is 1 we cannot select it. We will only search for even number so we don't need to worry with odd numbers.

Please suggest me how to solve this algorithm. Thanks in advance.

Answer 1

Pure SQL way to do it, which probably isn't that efficient:-

SELECT CONCAT_WS(',', a.id, b.id, c.id, d.id)
FROM tbl_money a
INNER JOIN tbl_money b ON b.id > a.id
INNER JOIN tbl_money c ON c.id > b.id
INNER JOIN tbl_money d ON d.id > c.id
WHERE a.amount + b.amount + c.amount + d.amount = 14
UNION
SELECT CONCAT_WS(',', a.id, b.id, c.id)
FROM tbl_money a
INNER JOIN tbl_money b ON b.id > a.id
INNER JOIN tbl_money c ON c.id > b.id
WHERE a.amount + b.amount + c.amount = 14
UNION
SELECT CONCAT_WS(',', a.id, b.id)
FROM tbl_money a
INNER JOIN tbl_money b ON b.id > a.id
WHERE a.amount + b.amount = 14
UNION
SELECT a.id
FROM tbl_money a
WHERE a.amount = 14

EDIT - modified to check the used field, and also that the amounts are not odd numbers

SELECT CONCAT_WS(',', a.id, b.id, c.id, d.id)
FROM tbl_money a
INNER JOIN tbl_money b ON b.id > a.id AND b.used = 1 AND MOD(b.amount, 2) = 0
INNER JOIN tbl_money c ON c.id > b.id AND c.used = 1 AND MOD(c.amount, 2) = 0
INNER JOIN tbl_money d ON d.id > c.id AND d.used = 1 AND MOD(d.amount, 2) = 0
WHERE a.amount + b.amount + c.amount + d.amount = 14
AND a.used = 1
AND MOD(a.amount, 2) = 0
UNION
SELECT CONCAT_WS(',', a.id, b.id, c.id)
FROM tbl_money a
INNER JOIN tbl_money b ON b.id > a.id AND b.used = 1 AND MOD(b.amount, 2) = 0
INNER JOIN tbl_money c ON c.id > b.id AND c.used = 1 AND MOD(c.amount, 2) = 0
WHERE a.amount + b.amount + c.amount = 14
AND a.used = 1
AND MOD(a.amount, 2) = 0
UNION
SELECT CONCAT_WS(',', a.id, b.id)
FROM tbl_money a
INNER JOIN tbl_money b ON b.id > a.id AND b.used = 1 AND MOD(b.amount, 2) = 0
WHERE a.amount + b.amount = 14
AND a.used = 1
AND MOD(a.amount, 2) = 0
UNION
SELECT a.id
FROM tbl_money a
WHERE a.amount = 14
AND a.used = 1
AND MOD(a.amount, 2) = 0