More efficient way of finding every possible combination

Question

this is a bit tough for me to explain, I'm trying to get every possible combination of numbers while only using the numbers all together once, for example, if I have the numbers 1 through 10 and want the unique combinations in groups of 3:

1,2,3
4,5,6
7,8,9

These are fine, but I'm currently doing a Cartesian join on some tables, so for the first group I'm getting:

1,2,3
3,2,1
1,3,2
2,3,1
2,1,3

etc... since I've already used 1,2,3 once, I don't want all the other combinations of it.

This is the code I'm currently using, I'm not quite sure how to do what I want in SQL. The id1,id2,id3 are the 3 numbers I'm trying to find all possible combinations of.

INSERT INTO recipe_index
SELECT distinct '3' as nummeals, t1.id as id1, t2.id as id2, t3.id as id3,   
t1.calories+t2.calories+t3.calories as calories, t1.protein+t2.protein+t3.protein as  
protein, t1.carbohydrate+t2.carbohydrate+t3.carbohydrate as carbohydrate, 
t1.fat+t2.fat+t3.fat as fat from recipes t1, recipes t2, recipes t3

I hope what I'm trying to accomplish here makes some sense..

SQL is set-based; I don't think this is the best use of SQL. — duffymo, Oct 23 '12 at 22:29
I will say, this would be something of a nightly job, so it's not ran regularly. I need to get it up from it's current state (group of 3 numbers) to up to a group of 6 numbers (each number between 1 - 80 currently), so the amount of results at that point is extremely high, so I'm trying to find a more efficient way to do it in SQL (or otherwise). — Neostim, Oct 23 '12 at 22:37
Agree with previous commenter that this isn't a great fit for SQL. That said, what you're doing is taking a mathematical [combination](http://en.wikipedia.org/wiki/Combination) of the members of the set you're retrieving from the database. It probably would be better to do this in code than in SQL. — TAH, Oct 23 '12 at 22:48

mjv · Answer 1 · 2012-10-23T22:54:13.430

INSERT INTO recipe_index
SELECT t1.Id as t1Id, t2.id as t2Id, t3.id as T3id
   t1.calories+t2.calories+t3.calories as calories, 
   t1.protein+t2.protein+t3.protein as protein, 
   t1.carbohydrate+t2.carbohydrate+t3.carbohydrate as carbohydrate, 
   t1.fat+t2.fat+t3.fat as fat 
FROM recipes t1
JOIN recipes t2 on T2.id < t1.Id
JOIN recipes t3 on t3.id < t2.id

This will produce all the combinations of three Ids taken among all the recipe ids, without producing any duplicate (if we consider duplicate to also include sequences the same IDs but in a different order).

Now, if I understand the problem, we want to pull out of recipe_index all combinations of rows that use all Ids once and only once. (note, the use exactly all ids once and only once, the implication is that the number of recipes is a multiple of 3).

score 1 · Answer 2 · answered Oct 23 '12 at 22:40

I do not know how good this is but dose the job:

INSERT INTO recipe_index
SELECT distinct '3' as nummeals, t1.id as id1, t2.id as id2, t3.id as id3,   
t1.calories+t2.calories+t3.calories as calories, t1.protein+t2.protein+t3.protein as  
protein, t1.carbohydrate+t2.carbohydrate+t3.carbohydrate as carbohydrate, 
t1.fat+t2.fat+t3.fat as fat from recipes t1 inner join  recipes t2  on t1.Id < t2.Id inner join  recipes t3  on t2.Id < t3.Id

More efficient way of finding every possible combination

2 Answers2