Query records' combinations

programming
1

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 :slight_smile:

2

[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?

3

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:

4

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
5

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

6
  1. how do i get/output the results?
  2. how do i use it for x records? Not only for 4…
  3. Thanks again :smiley:
7
  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
8

These permutations are what I’m still looking for.

Thanks for the try. :slight_smile:

9

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