Eeek! Joining like that limits the number of things you can compare to the number of joins. And it's terrible for performance as your tables have more rows.
There is another way. Recursive SQL.
This will maintain two things
- The total weight so far
- A list of the pizza toppings you've already checked
Obviously we need the weight, because that's what we're measuring. The list is to prevent us checking the same row twice.
So we want to start with all the rows in the table. You could exclude those <= 5kg at this point if necessary. Wrap the topping with hashes. This ensures 1 is not confused with 11, 21, etc. (#1# <> #11#, but 1# is part of 11#, 21# and so on).
select topping, '#' || topping || '#', weight tot_wt from t
To build the recursive case, we now:
Join the original table where:
- The topping is not in the list already considered
- The total weight + new weight <= 5
- The current topping number is greater than those considered. This is to prevent double counting 2,3 and 3,2
For each row that passes these checks, append its topping to the list of those already processed and increment the total weight:
select t.topping, toppings || t.topping || '#', tot_wt + weight tot_wt
from tots
join t
on instr(toppings, '#' || t.topping || '#') = 0
and tot_wt + weight <= 5
and tots.topping < t.topping
Put it all together and you have:
create table t (
topping int,
weight int
);
insert into t values ( 1, 5 );
insert into t values ( 2, 3 );
insert into t values ( 3, 2 );
insert into t values ( 4, 4 );
with tots (topping, toppings, tot_wt) as (
select topping, '#' || topping || '#', weight tot_wt from t
union all
select t.topping, toppings || t.topping || '#', tot_wt + weight tot_wt
from tots
join t
on instr(toppings, '#' || t.topping || '#') = 0
and tot_wt + weight <= 5
and tots.topping < t.topping
)
select toppings, tot_wt from tots;
TOPPINGS TOT_WT
#1# 5
#2# 3
#3# 2
#4# 4
#2#3# 5
This gets you all the combinations that weight up to 5kg. Add a 1kg option and it still works:
insert into t values ( 5, 1 );
with tots (topping, toppings, tot_wt) as (
select topping, '#' || topping || '#', weight tot_wt from t
union all
select t.topping, toppings || t.topping || '#', tot_wt + weight tot_wt
from tots
join t
on instr(toppings, '#' || t.topping || '#') = 0
and tot_wt + weight <= 5
and tots.topping < t.topping
)
select toppings, tot_wt from tots;
#1# 5
#2# 3
#3# 2
#4# 4
#5# 1
#2#3# 5
#2#5# 4
#3#5# 3
#4#5# 5
If you want just those that are exactly 5kg, add this to the final where clause!