MYSQL - How can i check if there is one possible combination of value that fits a range

I am trying to run an algorithm to include / exclude a number of appartments depending if there is a total amount of square meters to fit a criteria.

Here's an example with a set of data :

CREATE TABLE lot
( `id` int(11), `surface_dispo` int(11));


INSERT INTO lot
    (`id`, `surface_dispo`)
VALUES
    ('1', '550'),
    ('2', '700'),
    ('3', '850');

Let's say that I want to find every possible combination that would fit between 1500 and 2000 square meters. In that example I should be able to retrieve the combination of lot N° 2 and 3, but not 1 2 and 3

I have read some topics about recursive fonctions in MYSQL but I couldn't get to convert examples I found because they were too specifically about string concatenation, and not SUM.

Please also note that the SUM of those lots could be with N numbers involved, let's say that i want to find all of the available surfaces between 2000 AND 2500, I should retrieve 1 + 2 + 3.

Thank you in advance for your help, I will provide any further information needed.

Solution

Possible solution (a sample):

CREATE TABLE test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  val INT
);
INSERT INTO test (val)
WITH RECURSIVE cte AS (
  SELECT 1 num UNION ALL SELECT num + 1 FROM cte LIMIT 10
)
SELECT 1000 * RAND() FROM cte;
SELECT * FROM test;

id	val
1	745
2	992
3	726
4	651
5	80
6	445
7	985
8	590
9	995
10	203

SET @minimal := 2000;
set @maximal := 2100;

WITH RECURSIVE cte AS (
  SELECT id, val AS total, CAST(id AS CHAR(65535)) rowset FROM test
  UNION ALL
  SELECT test.id, cte.total + test.val, CONCAT_WS(',', cte.rowset, test.id)
  FROM cte
  JOIN test ON test.id > cte.id AND cte.total < @maximal
)
SELECT rowset, total
FROM cte
WHERE total BETWEEN @minimal AND @maximal;

rowset	total
1,3,8	2061
2,4,6	2088
2,5,7	2057
2,5,9	2067
2,6,8	2027
4,6,7	2081
4,6,9	2091
5,7,9	2060
6,7,8	2020
6,8,9	2030
1,2,5,10	2020
1,4,5,8	2066
1,4,6,10	2044
1,5,7,10	2013
1,5,9,10	2023
2,3,5,10	2001
3,4,5,8	2047
3,4,6,10	2025
3,5,9,10	2004
5,6,7,8	2100
1,5,6,8,10	2063
3,5,6,8,10	2044

fiddle