# Query records' combinations

I got a simple SELECT query:

SELECT id
FROM myTable
WHERE id <= x;

and the results are:
1
2
3
4
.
.
x

and what i need is the combinations of (x-1), (x-2), …, 2

Let’s say x = 4.
The combinations of 3 and 2 records would be:

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

AND

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

Anyone has any idea of how this is possible?

PS: I’m using ColdFusion, but try any language serves you and I’ll translate it. :glasses:

Thanks

[QUOTE=amaze;1984156]Let’s say x = 4.
The combinations of 3 and 2 records would be:

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

AND

1,2
1,3
1,4
2,3
2,4
3,4
[/QUOTE]^ i’m lost as to what this means. can you give column titles to these values?

I only take 1 column and the column’s name is “id”.

the records/values I get from the SELECT QUERY are:
1(id=1)
2(id=2)
3(id=3)
4(id=4)

From these 4 records I also want to get the triads and couples…
It’s for a soccer bet application, where someone selects lets say 4 games and wants to bet on all triads(4 of them).

This is for x=4. I want the coding for any x.

Is this more clear? :sigh:

ah yes. for testing purposes, table name in my example is amaze_games:

``````
/* gets first result set */
SELECT	a.id,
b.id,
c.id
FROM	amaze_games a
INNER JOIN	amaze_games b
ON		a.id != b.id
AND		a.id < b.id
INNER JOIN amaze_games c
ON		b.id != c.id
AND		b.id < c.id

/* gets second result set */
SELECT	a.id,
b.id
FROM	amaze_games a
INNER JOIN	amaze_games b
ON		a.id != b.id
AND		a.id < b.id
``````

actually, you can probably take out the != conditions and just use the tbl1.id < tbl2.id

1. how do i get/output the results?
2. how do i use it for x records? Not only for 4…
3. Thanks again
1. that depends on what language your site is written in. i don’t know how to do it in CF.
2. hmm. i’m not sure how to approach using just SQL. not sure if it’s possible. it might be faster though to bring a [font=monospace]SELECT DISTINCT id FROM amaze_games[/font] into an array in your server-side language, then create the permutations there.
3. you’re welcome

These permutations are what I’m still looking for.

Thanks for the try.

i’ll think about it and let you know if anything comes to mind.